Excel実践に応用できる関数 

その他の関数

その他の関数一覧
    その他の関数として、動的処理を可能にできる関数を中心にピックアップして紹介します。

  QUOTIENT関数:数値を除数で割ったときの商を返します。
    MOD関数: 数値を除数で割ったときの剰余を返します。
    INT関数:数値の小数以下を最も近い整数に切り捨てます。
    ROW関数:セルの行番号を返します。
    COLUMN関数:セルの行番号を返します。
    INDIECT関数:指定される文字列への参照を返します。
    OFFSET関数:セルから指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。

 複数行で一つのグループのデータとして、取り扱いたい場合があります。
    そんな時に、計算式を効率よく組み立てていくために
    ①基準行から何行目かを知りたい。
    ②最初から、何番目のグループかを知りたい。
    ③グループの中で、何番目の行かを知りたい。
    と、いうことがあります。
    その時に使用する関数が、MOD関数、INT関数、ROW関数(COLUMN関数)
     この場合、それぞれのグループの構成数は全て同じでなければなりません。
ページトップへ
           Excelの動的処理に活用できる関数   

ページトップへ

QUOTIENT関数

QUOTIENT関数 は、数値を除数で割ったときの商を返します。

<書式>
        MOD(数値, 除数)
           数値:割り算の分子となる数値を指定します。
           除数:除算の分母となる数値を指定します。
          戻り値:整数値

<使用例>
   = QUOTIENT
(17,3) ⇒「5
    商を表す整数値が返される。

ページトップへ

MOD関数

MOD関数は、数値を除数で割ったときの剰余を返します。

<書式>
        MOD(数値, 除数)
           数値:割り算の分子となる数値を指定します。
           除数:除算の分母となる数値を指定します。
          戻り値:整数値

<使用例>
   =
MOD(17,3) ⇒「2
    余りを表す整数値が返される。

ページトップへ

INT関数

INT関数は、数値の小数以下を最も近い整数に切り捨てます。

<書式>
        INT(数値)
           数値:数値を指定します。
           戻り値:小数以下を切り捨てた整数値

<使用例>
   =
INT(17/3) ⇒「5
    計算結果の小数以下を切り捨てた、整数値が返される。

ページトップへ

ROW関数

ROW関数は、セルの行番号を返します。

<書式>
        ROW(セルの範囲)
           セルの範囲:セルのアドレス
          戻り値:セルの行番号を表す整数値

<使用例>
   例1    =
ROW(B115) ⇒「115」
            セルの行番号が返される。
    例2    B115セルに「=ROW()」と入力した場合は、「115」と値が返される。
             B1セルに「=ROW()」と入力した場合は、「1」と値が返される。
   例3   B25セルがB11行目から何行目であるか知りたい時、
           
B25セルに 「= ROW()-ROW(B10) 」と入力します⇒「15」が返されます。
            つまり、B25セルは、B11行目から15行目であることが取得できます。

ページトップへ

COLUMN関数

COLUMN関数は、セルの行番号を返します。

<書式>
        COLUMN(セルの範囲)
           セルの範囲:セルのアドレス
          戻り値:セルの行番号を表す整数値

<使用例>
   例1    =
COLUMN(B115) ⇒「2」
            セルの行番号が返される。
    例2    B115セルに「=COLUMN()」と入力した場合は、「2」と値が返される。
             C1セルに「=COLUMN()」と入力した場合は、「3」と値が返される。

ページトップへ

INDIECT関数

INDIRECT関数は、指定される文字列への参照を返します。セル参照され、セルの内容が表示されます。

<書式>
        INDIRECT(参照文字列, [参照形式]) 
           参照文字列 :セルの参照、範囲名の参照、または文字列としてのセルへの参照を指定します。
           参照形式:参照文字列で指定されたセルに含まれるセル参照の種類を、論理値で指定します。
                TRUE を指定すると、参照文字列には A1 形式(デフォルト値)
                FALSE を指定すると、参照文字列には R1C1 形式
          戻り値:値・セル範囲を返します。
ページトップへ

<使用例>
  
福岡市の各地区のイベント参加者の集計表があります。
    下図の通り、地区の範囲名が付けられています。(博多区、中央区、城南区・・・)
    B2セルには、地区名の選択できる様に入力規則が設定されています。
    B3セルには、B2セルの地区名が「城南区」選択されると、城南区の合計が集計される。
    また、「博多区」と選択されれば、博多区の合計が選択される様に計算式を設定します。
    合計は、「SUM関数」で集計します。
    ここで、地区名が選択されると参照範囲も変化させるためにINDIRECT関数を使用します。
    「INDIRECT(B2)」とすることで、地区名の範囲を参照することができる様になります。
            参照セル「B2」は、参照方法を絶対参照(「$B$2」)にしています。
            B3セルには、「=IF($B$2="","",SUM(INDIRECT($B$2))) 」とすると
            地区の合計が取得できる様になります。
    動的処理が可能になる、優れものの関数です。
ページトップへ
    ExcelのINDIRECT関数の説明

ページトップへ

OFFSET関数

OFFSET関数は、基準から指定行・列数だけシフトした位置にあるセル範囲の参照を返します。

<書式>
        OFFSET(基準, シフト行数, シフト列数, [参照の行数], [参照の列数]) 
           基準:基準となる参照セルを指定します。
           シフト行数:基準セルを上方向または下方向へシフトする距離を行単位で指定します。
                            複数セルの場合、基準の左上隅のセルが基準
           シフト列数:基準セルを左方向または右方向へシフトする距離を列数単位で指定します。
                            複数セルの場合、基準の左上隅のセルが基準
           参照の行数:オフセット参照の行数を指定します。(正の数)(省略可)
                             基準のセル範囲と同じ行数
           参照の列数:オフセット参照の列数を指定します。(正の数)(省略可)
                             基準のセル範囲と同じ列数
          戻り値:数値または範囲          

ページトップへ

<使用例>
   例1:学校の交通安全の当番を取得できる様に計算式をB6セルに入力します。
            Excelの動的関数INDIRECT関数  

基準 A9セル($A$9(絶対参照))
シフト行数 学年の数値「5」分だけ下方向へシフト⇒5行下へ
シフト列数 クラスの数値「4」だけ右方向へシフト⇒4列右へ
参照の行数 1(省略可)
参照の列数 1(省略可)
戻り値 「新井」

         計算式       =OFFSET($A$9,$B$4,$B$5,1,1)
           上の図の場合は、5学年の4組なので下方向に5右方向に4だけシフトします。
            参照の行数と列数はともに「1」なので、「新井」という値が戻り値として渡されます。

ページトップへ

<使用例>
   例2:学校の交通安全の学年代表をリスト入力できる様に入力規則をB7セルに設定します。
              ExcelのOffset関数を入力規則

ページトップへ

       計算式       =OFFSET($A$9,$B$4,1,1,5)

基準 A9セル($A$9(絶対参照))
シフト行数 学年の数値「5」分だけ下方向へシフト⇒5行下へ
シフト列数 1(開始位置へ)
参照の行数 1
参照の列数 5(1組~5組までなので)
戻り値 図の場合、5年生の代表なのでB14:F14の範囲の値
           B7セルにリストで選択できる様に、入力規則のフォームの元の値の枠内
            「=OFFSET($A$9,$B$4,1,1,5)」を設定する。
          すると、下図の様に5年生の、リストが表示されました。
            学年を入力すると、自動的に学年に応じたリストが表示される様になります。 
            ExcelのOFFSET関数を入力規則に活用

「OFFSET関数」を使用することにより、動的に参照元を切り替えることができる様になります。
「OFFSET関数」の使用用途は、上記外にもたくさん存在します。
「OFFSET関数」は、動的処理を可能にしてくれる素晴らしい関数です。
ビジネスで活用したいと願う方には、是非マスターして欲しいテクニックの一つです。

ページトップへ