=INDIRECT(参照文字列)
■文字列として入力したセル番地や範囲名を、計算式で参照できる形に変換する。参照先を文字列で間接的に指定し、切り替え可能にする
INDIRECT関数は文字を結合する「＆」で参照用の文字を作成してセル参照に変換します。引数［参照文字列］の中には、文字や数字だけでなく、セル番地を指定できます。「この文字を」ではなく、「この文字列に書いてあるセル」という認識になります。
【単価シート】
INDIRECT関数を使用してC１の文字列を使用して【単価シート】を指定してB2の値を転記する
このようにセルにある文字列を使用して、参照先を指定できるのがINDIRECT関数になり、他の関数と組み合わせて使用することと便利です。
=MATCH(検査値,検査範囲,照合の型)
■「検査値」が「検査範囲」の先頭から何番目にあるかを数値で示す。「照合の型」を「0」にすると、「検査値」と完全に一致する値を探す
=INDEX(範囲,行番号,列番号)
■上から何行目かを示す「行番号」と、左から何列目かを示す「列番号」を指定し、「範囲」の中から該当位置にあるデータを取り出す
行と列の項目名を指定してデータを表示する方法
記号表から、月(行)と日(列)を指定して、該当する記号を表示します。このような場合に、MATCH関数とINDEX関数の組み合わせて使うと便利です。
セル(N２)とセル(N３)に、行と列のそれぞれの項目名を入力すると、該当する記号が表示されます。
=INDEX(範囲,行番号,列番号)
■上から何行目かを示す「行番号」と、左から何列目かを示す「列番号」を指定し、「範囲」の中から該当位置にあるデータを取り出す
掛け算データから列番号と行番号を指定して、データを算出する。
九九の掛け算をまとめた表があります。この表のを使って、列番号と行番号のセルに入力してそれぞれの位置にある結果を算出します。
表の７行目８列目で指定した位置にあるデータを調べたい場合に、ＩＮＤＥＸ関数を使用して、引数「範囲」で指定した表を、引数「行番号」「列番号」で指定した位置にあるデータを転記します。
ここでは、表中の７行８列目にある「５６」が表示されます。
=MATCH(検査値,検査範囲,照合の型)
■「検査値」が「検査範囲」の先頭から何番目にあるかを数値で示します。「照合の型」を「０」にすると、「検査値」と完全に一致する値を探す
データが表の何番目にあるか検索する
入力した名前が、メニューリストの先頭から何番目にあるか知りたい場合に、選択した表の何番目にあるかをMATCH関数を利用して検索します。
ＭＡＴＣＨ関数の引数「検査値」に名前のセル、「検査範囲」に会員名が入力されているセル範囲、完全に一致するデータを探すので「照合の型」を「０」にします。ＭＡＴＣＨ関数は、横方向で何番目かを調べることもできる。この場合、左から数えた位置になります。
=INDIRECT(参照文字列)
■文字列として入力したセル番地や範囲名を、計算式で参照できる形に変換する。参照先を文字列で間接的に指定し、切り替え可能にする
=VLOOKUP(検索値,範囲,列番号,検索の型)
■「検索値」を「範囲」の左端列で探し、見つけた場所から右へ「列番号」の位置にある値を取り出す。「検索の型」が「FALSE」のときは、「検索値」と完全に一致する値を探す
複数の表を切り替えて転記する
[名前付き範囲を作成する(表に名前を付ける)]
[INDIRECT関数で表を指定する]
栃木青果市場と群馬青果市場の2つの別表があり、名前の定義で「栃木」「群馬」と名前を作成します。値によって、ＶＬＯＯＫＵＰ関数を使用して、INDIRECT関数で転記元の表を切り替えます。
=VLOOKUP(検索値,範囲,列番号,検索の型)
■「検索値」を「範囲」の左端列で探し、見つけた場所から右へ「列番号」の位置にある値を取り出す。「検索の型」が「FALSE」のときは、「検索値」と完全に一致する値を探します。
別シートの表から野菜名や単価を転記する
【別シート(シート元)】
シート元！$B$3:$D7
【転記シート(シート先)】
5-02では同じシートにある別表からデータを転記しましたが、別のシートにある場合でも転記は可能です。変更される点として、シート参照先の指定をすることです。
条件に合致したデータを別表から転記するには、１４４ページのようにＶＬＯＯＫＵＰ関数を使う。参照先の表が別シートにあっても転記は可能だが、「範囲」の指定方法に注意
したい。別シートの場合、セル範囲の前に「シート名」と半角の「！」を付けて、「商品一覧! $A$ 3 :$C $ 8」のように記載する必要がある。
面倒な場合は、別表のセル範囲に名前を付けると（86ページ参照）、式を簡略化できる。
今回の例では、「商品一覧」シートのA3 〜C8セルに別表があるので、引数「範囲」は「商品一覧!$A$3:$C$8」とする。セル番地は、絶対参照にしておこう（13ページ参照）。あとは、これをほかのセルにコピーする
参照先の別表に、「名前」を付ける手もある。86ページの手順で「商品一覧」シート
のＡ３〜Ｃ８セルに「商品一覧表」と名前を付けると、式を簡略化できる。名前で指定する範囲は絶対参照となるので、このままドラッグしてコピーしよう
=IFERROR(値,エラーの場合の値)
■「値」に指定した式がエラーでないときはその結果を表示し、エラーの場合は「エラーの場合の値」に指定した内容を表示します
自動転記時に未入力時のエラー表示を消す
VLOOKUP関数の数式は、商品コードが未入力だと、エラーになってしまうので、IFERROR関数を使うと、エラー表示させないようにできます。
エラーの場合とそうでない場合とで値を変えるIFERROR関数を使って、エラーだった場合は空欄を表示するような式へ変更します。関数や計算式を作成して、空欄の場合はこのようなエラー表示になるケースが多々あるので、同じようにIFERROR関数をした方が良いです。
=VLOOKUP(検索値,範囲,列番号,検索の型)
■「検索値」を「範囲」の左端列で探し、見つけた場所から右へ「列番号」の位置にある値を取り出します。「検索の型」が「FALSE」のときは、「検索値」と完全に一致する値を探します。
商品コードが合致した野菜名と単価を同じシート内の別表から転記する
ＶＬＯＯＫＵＰ関数を使うと商品コードを入力すると、同じシートの別の位置にある一覧表から、該当する野菜名と単価を自動で転記します。
引数「列番号」を２にすると範囲選択した２列目にある野菜名を転記でき、３にすると３列目の単価を転記します。
なお「範囲」で指定する別表のセル範囲は、コピーしてもずれないように、絶対参照にしておこう。
このように条件に合ったデータを別表から転記するには、VLOOKUP関数を使います。「検索値」の商品コードの１の値を、別表のデータ部分（G3〜I7セル）の左端の列で探し、一致したG3セルと同じ行の2列目にある「じゃがいも」を表示します。
式をドラッグコピーする際に範囲指定の参照範囲がずれないよう絶対参照(＄マーク)にしておきます。
同じようにして、単価もVLOOKUP関数で転記する。この場合は、「列番号」を3にします。
=MOD(数値,除数)
■「除数」で「数値」を割ったときの余りを求める
日付を除数７で割ったときの余りで、曜日を数値化します。
今回は、ＭＯＤ関数を用いて、日付を７で割り余りを数値化して曜日と連動できます。その数値を使用して条件付き書式等や関数を組み合わせて、様々な用途で条件で利用できます。
例えば、条件付き書式で、土曜日と日曜日の0と1だった場合は表示はセルを赤表示にするなど色んな方法で工夫しましょう。
=FLOOR(数値,基準値)
■「数値」を、「基準値」の倍数のうち最も近い値に切り捨てる（0に近いほうの倍数にする）
=CEILING(数値,基準値)
■「数値」を、「基準値」の倍数のうち最も近い値に切り上げる（0から遠いほうの倍数にする）
30 分単位で切り捨て／切り上げする
給与計算の勤務時間等で端数を丸めたいことがある場合にこの関数を使えば簡単に計算できます。
基準値の倍数のうち最も近い値に切り捨てるＦＬＯＯＲ関数を使い、例えば実働時間が「8：05」だった場合、30分単位で「８:00」になるように、時間の「分」の値を30分単位で切り捨てます。
逆に「8:05」が「8：30」になるように、30分単位で切り上げるには、ＣＥＩＬＩＮＧ関数を使います。