!!! Excel.tips Excelを使う時のちょっとしたこと {{outline}} !!Tips *すべてのカラムを選んで、カラムとカラムの間をダブルクリックすると、カラムの幅を自動調節してくれる。 *選択範囲を、ずずっと下まで指定: Ctrl+Shift+↓ *パターンのある操作を繰り返すには:マクロの記録、あとは、Ctrl+指定の文字 *複数の連続したセルの中身を消す:選んで「Del」キー(Bkspキーではダメな点に注意) *セルに斜め線を入れる:セルを選んで、右ボタン「セルの書式設定」で「罫線」 !!ゼロで始まる番号 *学生番号など、0で始まる数字の場合、そのままだと、先頭の0は表示されない。ないものとして、扱われてしまう。 *最初から、セルの書式を「文字列」にしてから、入力し始めるように。 *でも、既存のデータをファイルでもらって、開けたら、0が消えていたという場合、どうやって復活するか。 +該当箇所を、エディターなどへコピペ +正規表現で行頭に0を挿入する(行頭^を0で置き換える=行頭に0を挿入) **検索文字列:^ **置換文字列:0 +エクセルの表の該当箇所の書式を「文字列」にしておく +エディターで置き換えたものを、エクセルにコピペ !!欠損値 !そこだけデータがないときの扱い *エクセルでは欠損値を表す「記号」はない。(「#N/A」は値がないことを示すエラー表示) *そこの値をデータに含めるかどうかは、目的次第。 **含めない場合は、空欄にしておく(0という数字を入れないように)(セルの背景をグレーなどにしておくとわかりやすい) **含める場合、つまり、値がないことはそこが0という値であることを示す必要がある場合、数値 0 を入れておく。 *たとえば、平均値を出すときに、その範囲内に欠損値のセルがあるとしたら、 **空欄なら、計算の対象からはずして平均を出す。 **0 になっていると、その分、平均値が下がることになる。 !!Excelで特定の値(のセル)に色をつける +色をつけたい範囲を選んでおく。 +「ホーム」から、右の方、「条件付き書式」を選ぶ。 +一番上の「セルの強調表示ルール」から、 +「指定の値に等しい」を選ぶ。(文字列の場合は「文字列」を選ぶ) +左に数字を入れて、右で書式を選ぶ。(一番下、「ユーザ設定の書式」で選ぶ。) +「OK」を押す {{ref_image GroupA.png}} !!エクセルで文字列を逆にする (2003-08-01 sugiura) !--- 文字列を後方からソートするには --- !ポイント *ユーザー関数をつくる *VBAにそなわっている文字列を逆にする関数「<>」を使う !手順 +エクセルでワークシートを開く。 +メニューの「ツール」から「マクロ」を選び、そこから「Visual Basic Editor」を選ぶ。 +新しく見慣れないウインドウが開く。それが Visual Basic というプログラムを書くところ。 +Visual Basic のウインドウのメニューの「挿入」から「標準モジュール」を選ぶ。 +「Module (コード)」という新しいウインドウが開く。そこにプログラムを書いていく。 +そのなかに以下のように書く: Function reverse(v) reverse = StrReverse(v) End Function +Visual Basic のウインドウのメニューの「ファイル」から「終了して Microsoft Excel へ戻る」を選ぶ。 +Visual Basic のウインドウが消えて、もとのエクセルの画面になる。 *これで、「reverse」という名前の関数が使えるようになっている。 *使い方は、他の一般の関数と同じ。 *例えば、A1のセルに「あいうえお」と入っているとして、その右側のB1 のセルに「=reverse(A1)」と書き入れ、「Enter」キーを押せば、B1の セルには「おえういあ」と表示される。 *単語リストが縦に並んでいるとして、その右側にこの関数を使って、 単語の文字列を逆順にして並べて、その逆順の文字列で並べ替えをすれ ば、もとの単語の後方から並べ替えたことになる。 !!成績の集計、評価 *60点未満     :F *60点以上70点未満:C *70点以上80点未満:B *80点以上90点未満:A *90点以上     :S !IF関数を入れ子にする =IF(Q4>=90,"S",IF(Q4>=80,"A",IF( Q4>=70,"B",IF( Q4>=60,"C","D")))) *最後は、それ以外の場合は"D"ということ *EXCEL 2016より新しいバージョンでは 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) !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) *参照先が別のシートの場合は範囲の前に「シート名!」をつける *<<具体案>> **シートを別に作っておいて、そこに基準を貼り付けて、そこを絶対参照するようにする。 =VLOOKUP(評価対象セル,Score!$A$1:$B$11,2,FALSE) *LibreOfficeCalcの場合は「$シート名.」 ***また、最後のところ、FALSEではなく0