関数で使える小技
ワイルドカード
カードゲーム等でワイルドカードというと『何にでもなれるカード』であるのはご存知だと思います。
エクセルでもこの『ワイルドカード』がつかえる関数と場面があります。
エクセルでは『一部分が合致していれば他は何でも』という時の『何でも』の部分に『ワイルドカード』を使います。
エクセルで『ワイルドカード』は『*』(半角のアスタリスク)を使います。
使える関数は『COUNTIF』が代表的ですが他にも『MATCH』『SUMIF』ちょっと異例ですが『VLOOKUP』で使えます。
範囲の中から特定のものを探し出す関数で使えるんです。
ただし、探し出すのは文字列で『ワイルドカード』を使うケースは『特定の文字を含むけど他の文字も含んでいるかもしれない』場合です。
では関数のどこで使うのか下表をご覧下さい。
一番上はCOUNTIF関数で『最後に県』の文字が付いているデータ数をカウントしています。
二番目はMATCH関数で『都』の前後に『*』が付いていますので『文字列のどこかに都』の文字がついていて範囲内の一番最初に出てきた位置を返しています。
三番目、四番目も考え方は同様で『*』の部分は何でも良いということです。
『*』の位置は『頭に付ければ最後に〇〇』『前後に付ければ〇〇が含まれ』『末尾に付ければ最初に〇〇』となります。
〇〇（文字）の部分はセル指定で『”*”&セル』とすることも可能です。
セル指定すると数式を触らずに可変させられるので便利かもしれませんね。
相対参照と絶対参照
関数を含めエクセル上で『セル指定』や『セル範囲指定』をする場合には『相対参照』と『絶対参照』は絶対に覚えて使い分けるべきです。
『小技』の中に入れはしましたが『必須』と言っても過言では無いと思います。
他の関数解説でも触れていますが、とても大事なことなのであえて解説をしておこうと思います。
『相対参照』と『絶対参照』を使い分けることが出来れば、関数を入れたセルをコピーする作業が数段効率良くなります。
エクセルでは『セル指定』の入った計算式を他のセルにコピーした時に自動的に『セル指定』を相対的に変化させてくれます。
これが『相対参照』といわれるものです。
逆に、式を他のセルにコピーしたとしても、相対的な変化をさせたくない時に使うのが『絶対参照』というものです。
『相対参照』と『絶対参照』の見た目の違いはセル指定に『$マーク』が付いていないか付いているかの違いです。
前に出てきたサンプルですが、セル範囲で『絶対参照』にしています。
私が関数で式を組み込む時には積極的に『絶対参照』を使います。
何故かというと関数を入れたセル位置を変えたい時に『相対参照』のままでは、式を書き直さなければならなくなり、無駄な時間を費やしてしまうことになるからです。
例えばA2セルからA10セルまでを合計する計算式をA11セルに入れる場合、A11セルにSUM関数で次のように入力すると思います『=SUM(A2:A10)』しかし、「やっぱりA12に計算式を入れたい」としたとき。
このままA11の式をA12にコピーすると式の中身は『=SUM(A3:A11)』と勝手に参照するセルが移動してしまいます。これが『相対参照』という機能です。
でもこれだとA2セルが計算からはずれてしまいますので、手作業でA3セルをA2セルに書き換えている方も少なくないのではないでしょうか。
この計算式のセル移動を意図的に止めて参照先が移動しないようにするのが『絶対参照』という機能です。
絶対参照を設定するにはセル範囲を指定する時に『$』を列のアルファベットの前や行の数字の前に付けます。この『$』を付けた列や行はどこに式を移動させても変動しません。
この『$』マークは手入力しても構いませんが「いちいち手打ちするのは面倒」という方はファンクションキーの『F4』を使って下さい。
セル選択やセル範囲を選択した後に『F4』キーを押して下さい。『F4』キーを押す回数によって次の様に変化してくれます。
１回(全固定) → ２回(行固定) → ３回(列固定) → ４回(固定無し)
$C$3:$C$49 → C$3:C$49 → $C3:$C49 → C3:C49
『$』を付ける場所は次の様に覚えましょう。
左右にコピーするなら列(アルファベット)の前に$
上下にコピーするなら行(数字)の前に$
上下左右にコピーするなら両方の前に$
これで他のセルにコピーしてもセル参照が移動せずに思ったセル範囲で計算を実行してくれます。
範囲指定のどちらも固定する場合もあれば、場面によっては片側セルだけ固定したい場合もあると思います。
その時は、コロン(：)の左右どちらかだけを固定することも可能です。→ $C$3:$C49
こんな風にC3は完全に固定しC49は列は固定するが行は移動してくれます。
片側だけの固定はROWS関数やSUM関数などで使うので下を参考にしてみて下さい。
セルへの入力で使える小技
文字列の数字
エクセルでセルに入力出来る数値の桁数や、セルの書式によって数値の表示されかたが変わってくるのはご存知ですか。
上の表は標準書式のセルと数値書式のセルでの表示の仕方の違いを表してみました。
まず標準書式の場合、数字をまともに表示できるのは11桁までで12桁以上になると上表の2番目の様なエラー表示になってしまいます。
数値書式にした場合は15桁までは正しく表示されますが、16桁以上は全て『0』となってしまい、表示だけでなく実際の数字も『0』としてしか扱われません。
私はこれで大きな間違いを犯しそうになった経験があります。
こんな桁数は通常では殆ど無く、特に金額でこの桁数は一般で扱うことはあり得ません。
しかし、カードなどの会員番号は実は16桁が多く使われていてデータベースからエクセルの数値セルにインポートすると全て下一桁が『0』になってしまいます。
ではどうやって回避すれば良いかというと、セルを文字列書式にすることで16桁以上も正しく表示することができます。
また、上表の様に16桁以上の数字を入力したい時は、先頭に『‘』半角のアポストロフィをつけるだけで文字列として入力することが出来ます。
『’』アポストロフィーは『半角モード』で[Shift]+[7]で入力します。
この方法は数字の頭に『0』を付けたい時などにも有効で、セル上は見た目に『‘』が表示されることもありません。
もちろんセルを文字列書式にすることで『‘』なしで数字が文字列として扱われます。
セルの書式設定はメニューリボンからか、セル上で右クリックして選択することも出来ますが、ショートカットキー[Ctrl]+[1]で設定画面が開くのでそちらがお勧めです。
セル内での改行
エクセルでセル内に文字や文章を入力する場面も多々あると思います。
しかし、エクセルで文章を入力する場合にネックとなるのがセル幅です。
当然、文章を入力する様なセルはそれなりの幅をとって作られていますが、やはり幅には制約があり文章をその幅に合う様に調整しているのではないでしょうか。
「それならセルの書式設定内の『配置』で『折り返して全体を表示』にすれば良い」確かにその方法もあります。
しかし、折り返しの位置が望んだ場所にならなかったり、印刷すると見た目と違った場所で折り返していたり、場合によっては２行で済むはずが３行になって一番下の文字が印刷されなかったりといった経験はありませんか。
そんな時には文章入力中に次の行にしたい場所で[Alt]+[Enter]と押して見て下さい。
セル内で改行することが出来てしまいます。
エクセルのフォントによっては文章がセル内に収まっている様に見えて、印刷すると表示しきれていない場合があるので、印刷プレビューで必ず確認しましよう。
複数のセルに同じ値を一括入力
複数のセルに同じ値を入力した経験はありませんか。
３ヶ所や４ヶ所なら一つ一つ入力しても大差は無いかもしれませんが、何十何百と入力しなければならない時はどうでしょう。
最も効率的なやり方を解説していきます。
上表の参加欄の空白になっている部分に『未定』と入力してみます。
まず空白を埋めたい範囲全体を選択します。
次にショートカットキーの[Ctrl]+[G]で『ジャンプ』ダイアログを開きます。
ダイアログが開いたら[Alt]+[S]で右側の『選択オプション』が開くので次に[Alt]+[K]で『空白セル』を選択し[Enter]をおすと次の様に空白だけが選択されます。
そのまま『未定』と入力し最後に[Ctrl]+[Enter]と押すと空白セル全てに『未定』の文字を入力することが出来ます。
入力後のセル移動をコントロール
セル選択を移動させたくない
さて最後はセルに値を入力した時に[Enter]キーを押すと思いますが、[Enter]をおした途端に次の行に選択が移ってしまいませんか。
時には入力したセルに「セル選択が留まっていて欲しい」ことってあるのではないでしょうか。
そんな時には[Ctrl]を押しながら[Enter]を押してみて下さい。
セル選択が移動せず元のセルに留まってくれます。
セル選択を逆方向に移動させたい
もう一つはセル選択の逆移動です。
通常は上から下に向かって入力していると思いますが、これも時には「下から上に向かって入力したい」ことがあります。
一ヶ所入力したらセル選択が下に移動するので[↑]キーで一つ上に移動させてまた入力しを繰り返しますのは非効率極まりないと思います。
そんな時は[Shift]を押しながら[Enter]を押しましょう。
セル選択が一つ上のセルに移動してくれます。
一つ一つは大したテクニックではありませんが、毎日のエクセルでの作業で使うことで圧倒的に効率が違ってきます。