【エクセル】関数に"負けない"（1／2）

エクセルの関数に負けない

エクセルを使い始めて最初の「壁」となるのが関数です。一口に関数といってもその数は500以上、どの場面でどの関数を使えば良いのか、どのように組み合わせれば目的の結果が得られるのか、関数が長くなるときにはどうやって対処すれば良いのかと悩みはつきません。

当方が関数を使う中で習得した「関数に負けない」テクニックについて説明します。

【目次】

1.知っていると便利な関数

2.関数が長くなるときの対処法

3.関数をコピー（オートフィル）するときの相対参照、絶対参照、複合参照

1.知っていると便利な関数

(1)テキスト操作関数

①text関数：ユーザー定義の書式を関数で実現できます。ユーザー定義の書式で表現できない書式の組み合わせも実現することが可能です。

②value関数：数字を文字列に変換する関数。関数で文字列から数字部分を切り出した際に合わせて使用します。

③trim関数：文字列の前後に含まれる不要な空白を削除する関数。関数で文字列から部分文字列を切り出した際に合わせて使用します。

④int関数：数値から小数低下を切り捨てた整数部分を取り出します。

⑤upper関数：文字列内の英字を大文字にします。

⑥lower関数：文字列内の英字を小文字にします。

(2)文字列操作関数

①subsutitute関数：文字を置き換えます。関数を入れ子にすることで、複数の文字を置き換えることができます。

②find関数：指定した文字列が現れる最初の位置を返します。mid関数等と合わせて使用します。（大文字・小文字を区別しない）

③search関数：指定した文字列が現れる最初の位置を返します。mid関数等と合わせて使用します。（大文字・小文字を区別しない）

④replace関数：指定した文字列を置き換えます。空白を取り除くことも出来ます。

(3)セル位置取得・指定関数

①row関数：セルの行番号を取得します。自身のセルの行番を取得することもできます。

②column関数：セルの列番号を取得します。自身のセルの行番を取得することもできます。

③offset関数：特定のセルを起点にして、指定行・指定列分離れたセルの値を取得します。離れた位置の繰り返しに規則性がある場合は、引数に数式を挿入したり、引数に数式を挿入したrow関数、column関数と組み合わせて使います。

(4)表内位置取得・検索関数

①index関数：先頭からの番号を指定してセル内の値を取得します。

②match関数：列内の文字列が一致する行番号を返します。match関数と組み合わせて、表内の値を取得します。vlookup関数で対応できない、右から左にへの検索、複数列から文字列を取得することもできます。

③vlookup関数：match関数とindex関数を組み合わせた結果を返します。完全一致(false)を指定すると不一致の場合にエラーを起こすため、基本的にiferrorで括って使います。iferror(vlookup(),"")。左から右の列しか参照できない。

④xloopup関数：複数列の検索や右から左への検索に対応したvlookup関数の進化版、Ooffice365から使用可能。

※これらの関数の参照先は絶対参照で範囲を明示して使いますが、範囲の代わりに列名を指定することが出来ます。参照先が可変の場合に便利です。

(例) =vlookup(a1,d:f,2,false) ←d～f列を参照しています。

(5)論理関数

①if関数：ifを入れ子にすると判読性が悪くなるため、判定条件にand関数（論理和）、or関数（論理積）を持ちいる入れ子を減らせます。

②ifs関数：一度に複数の判定条件を指定できます。

③switch関数：if関数の入れ子を回避します。Office365から使用可能。

(6)集計関数

オートSUM系の関数で有名な関数ですが、一歩進んだ使い方をすることができます。

①表全体の合計を取得します。

②Crtlで離れたセルの合計を取得します。

③関数が挿入された列から、関数のみの合計を取得します。

④表より1行分、1列分離れた範囲を指定して関数を挿入すると、空白セルをすべて関数で埋めてくれます。

⑤複数シートの集計をする場合は、最初のシートの対象セルを選択後、shiftキーを押しながら最後のシートのシート見出しを選択、括弧を閉じるとシートをまたいだ集計を行います。

⑥sumifs関数、countifs関数等：複数条件で集計を行うことができます。

⑦sumproduct関数で縦横集計ができます。

▶2/2へ続く

(C)猫さん78