Excel。指定したデータを1行分手早く表引き抽出するには、どうしたらいい【Table pull】

Excel。指定したデータを1行分手早く表引き抽出するには、どうしたらいい

＜INDEX関数＞

A4:D11に、店舗別の売上表があります。

A2にNOを入力したら、該当する、店舗名・地域・販売金額を表示したいわけです。

つまり、1行分のデータを表引きしたいわけです。

そこで、VLOOKUP関数をつかってという方法もあります。

ただし、VLOOKUP関数だと、3番目の引数の列番号にCOLUMN関数をつかうなど、アレンジしなければなりません。

XLOOKUP関数も同様です。

表引きできますが、面倒です。

オートフィルターをつかって抽出したデータをコピーして、貼り付ける方法もあります。

ただし、抽出するだけならばいいですが、抽出後に、別の場所に表示するとなれば、作業工程数が増えます。

そこで、今回はINDEX関数をつかって対応してみようと思います。

B2に数式を設定します。

=INDEX(B5:D11,A2,0)

最初の引数は、配列。範囲です。

B5:D11を範囲選択します。

スピル機能がうごきますので、絶対参照は不要です。

2つ目の引数は、行番号です。

A2のNOをつかいますので、A2と設定します。

3つ目の引数は、B列を起点としますので、0（ゼロ）と設定します。

では、数式を確定して結果を確認してみましょう。

このように、該当するデータの行全体を表引きすることができました。

スピル機能によって、オートフィルで数式をコピーする必要はありません。

この方法以外にはも、OFFSET関数を使う方法など、色々な方法があります。

今回は、その中から、INDEX関数をつかった方法をご紹介いたしました。

Excel。結合したセルにデータをスムーズにコピーするにはどうしたらいいの。【Integrated Cell】

Excel。結合したセルにデータをスムーズにコピーするにはどうしたらいいの。

＜INDEX+ROW関数＞

コピーして貼り付ける。

特に問題は無い操作だと思いますが、貼り付け先が結合されていると、スムーズに貼り付けることはできません。

次の表で確認してみましょう。

やりたいことは、A2の新宿は、C2の結合されたセルにコピーしたいわけです。

単純に、A2:A4を範囲選択して、C2を基準に貼り付けてみましょう。

すると、「この操作は結合したセルには行えません」とメッセージが表示されてしまいます。

では、セル参照とオートフィルで数式をコピーするというのは、ダメでしょうか。

C2をクリックして、=A2と数式を設定します。

オートフィルで数式をコピーしてみます。

やはり、うまくいきませんでした。

こうなると、一つずつ、コピーしなければならないのでしょうか。

それでは、時間がかかるだけでなく、面倒です。

そこで、数式を作って対応することにします。

C2をクリックして、次の数式を設定します。

=INDEX($A$2:$A$4,ROW(A2)/2)

あとは、オートフィルで数式をコピーします。

これで、対応することができました。

なぜ、この数式は対応することができるのでしょうか。

数式を確認しておきます。

INDEX関数は、

指定した列と行が交わる位置にある値を表示してくれる関数です。

最初の引数は、配列。

範囲選択なので、A2:A4。

オートフィルで数式をコピーしますので、$A$2:$A$4と絶対参照を設定します。

2つ目の引数は、行番号。

行番を求めることができるのが、ROW関数です。

ROW(A2)/2

と設定しましたが、この数式の意味は、A2の行番号は2です。

C列の新宿のセル番地は、C2となっています。

C列の品川のセル番地は、C4で、C列の横浜のセル番地は、C6となっています。

つまり、結合されているので、2・4・6という行番号です。

これを、2で除算します。

すると、新宿は2÷2で1。品川は4÷2で2。と算出できます。

範囲はA2:A4で、1行目と交差するのは、新宿というデータをINDEX関数で、表示できるという仕組みです。

この数式をつかうことで、結合セルに対応した、データのコピー（のようなこと）ができるというわけです。

Excel。ファイル内のシートは何枚あるの。このシートは何枚目にあるのがわかります。【SHEETS】

Excel。ファイル内のシートは何枚あるの。このシートは何枚目にあるのがわかります。

＜SHEETS関数・SHEET+INDIRECT関数＞

ファイル内のシートが多くなると、いったい、何枚あるのか数えるのは面倒です。

そこで、SHEETS関数をつかうことで、現在何枚のシートで構成されているのかを確認することができます。

用意したのは、次のファイルです。

シート管理・7月・8月・9月・四半期集計という5枚のシートで構成されています。

B2にSHEETS関数の数式をつくります。

=SHEETS()-1

SHEETS関数は、現在のファイルのシート数を算出してくれる関数です。

シート管理シートを除きたいとしたら、「-1」とすることで、シート枚数の算出結果を増減することもできます。

これで、シート数を数えられることが確認できました。

では、そのシート名が何枚目にあるのか確認したい場合、どのようにしたらいいのか説明します。

B5には、次の数式を設定しました。

=SHEET(INDIRECT(A5&"!a1"))

オートフィルで数式をコピーしています。

これで、それぞれ左から何枚目にあるのかがわかります。

この場合、SHEETS関数ではなく、SHEET関数をつかいます。

SHEET関数は、参照されたシートのシート番号を求めることができる関数です。

つまり、左から何枚目にあるのかを求めることができる関数というわけです。

SHEETS関数は、枚数を求める関数なので、「S」のありなしで、使い方が変わります。

では、 数式を確認してみましょう。

SHEET関数の引数に、INDIRECT関数を使用しています。

指定する文字列を直接参照することができる関数です。

なので、A5の値。

7月という文字そのものをつかうことができますので、

「A5&"!a1"」というのは、「7月!a1」という意味です。

「a1」はセル番地なので、「c1」でも構いません。

よって、7月のシートは、左から2枚にあることがわかりました。

ちなみに、シートを移動すると、算出された値も連動してかわります。

また、シート名が見つからない場合には、#REF!というエラーが表示されます。

Excel。INDEX関数のセル範囲形式は、セル範囲から縦横座標で値を抽出します【INDEX】

Excel。INDEX関数のセル範囲形式は、セル範囲から縦横座標で値を抽出します

＜関数辞典：INDEX関数のセル範囲形式＞

INDEX関数

読み方： インデックス　　

分類： 検索/行列 

INDEX(参照,行番号,[列番号],[領域番号])

セル範囲から縦横座標で値を抽出します

セル範囲形式

Excel。INDEX関数の配列形式は、セル範囲から縦横座標で値を抽出します【INDEX】

Excel。INDEX関数の配列形式は、セル範囲から縦横座標で値を抽出します

＜関数辞典：INDEX関数の配列形式＞

INDEX関数：配列形式

読み方： インデックス　　

分類： 検索/行列 

INDEX(配列,行番号,[列番号])

セル範囲から縦横座標で値を抽出します

Excel。オートフィルターをつかわずに、該当するデータを別のセルに表示したい【Extract】

Excel。オートフィルターをつかわずに、該当するデータを別のセルに表示したい

＜IF＋ROW関数　IFERROR＋INDEX＋SMALL＋ROW＋COLUMN関数＞

データベースや表から、該当する条件のデータを抽出して、別のセルに表示したい場合、オートフィルターでデータを抽出して、コピーをする。

この作業でもいいのですが、抽出条件が変わるとなると、オートフィルターで抽出してコピーするという作業が面倒になってきます。

そこで、数式だけで、対応することもできます。

A1:C8にデータがあって、G1のクラス名に合致するデータを、F4を起点として抽出表示したいわけです。

G1を「B」にしたら、クラスBのメンバーが抽出され表示するというものです。

最初は準備として、D2に次の数式を設定し、D8までオートフィルで数式をコピーします。

=IF(B2=$G$1,ROW()-1,"")

B列のデータと、G1のデータが合致したら、行番号-1を算出するという数式ですね。

ROW関数は行番号を算出します。

「-1」するのは、あとでINDEX関数をつかうのですが、座標がわかりやすくするために、「1」からの値にしたいからです。

今回は、データの1件目が2行目にありますので、「-1」すれば、「1」から表示することができます。

F4にメインとなる数式を設定します。

設定したら、オートフィルで数式をコピーします。

=IFERROR(INDEX($A$2:$C$8,SMALL($D$2:$D$8,ROW(A1)),COLUMN(A1)),"")

長い数式ですが、これだけで、該当するデータを抽出し表示することができます。

F4:H8まで数式を設定してあります。

それでは、数式を確認していきます。

最初のIFERROR関数ですが、データの件数が少ない場合、「#NUM!」というエラーが表示されてしまうので、その防止のためにIFERROR関数をつかっています。

INDEX関数は、設定した範囲の中から、指定した行番号と列番号のデータを抽出するという関数です。

最初の引数は、「配列」です。

「$A$2:$C$8」と設定します。

オートフィルで数式をコピーしますので、絶対参照を忘れないようにしましょう。

2番目の引数は、「行番号」です。

SMALL($D$2:$D$8,ROW(A1))

先程、IF関数で算出した結果を、SMALL関数を使い、1番小さい値を抽出させます。

1番小さい値、次に2番目の小さい値というようにしたいので、SMALL関数をつかい、さらに、ROW関数でA1を指定します。

オートフィルで数式をコピーすると、A1がA2とかわり、2番目・3番目に小さい値のある場所を見つけてくれるという仕組みです。

最後の引数は、「列番号」です。

COLUMN(A1)

こちらも、先程と同じ仕組みで、オートフィルで数式をコピーするときに、座標がズレるようにしています。

このように、INDEX関数をつかうことで、データを抽出することもできます。

Excel。VLOOKUP関数で抽出した文字からフリガナを表示させたい【furigana】

Excel。VLOOKUP関数で抽出した文字からフリガナを表示させたい

＜VLOOKUP関数・PHONETIC+INDEX+MATCH関数＞

氏名からフリガナを抽出したい時には、PHONETIC関数をつかうことで、表示できます。

ところが、PHONETIC関数は、文字を入力した時のデータを表示するため、VLOOKUP関数やセル参照などで、間接的に表示されているデータからフリガナ情報を表示することができません。

では、どのようにしたら、次のように、フリガナを表示することができるのでしょうか。

B2には、VLOOKUP関数の数式を設定してあります。

=VLOOKUP(A2,A5:F14,2,FALSE)

C2に、PHONETIC関数をつかった数式を設定してみても、何も表示されません。

C2に、

=PHONETIC(B2)

と設定していますが、表示されないわけですね。

解決するポイントは、文字入力が入っている元のデータをつかえばいいわけです。

では、次のようにC2の数式を修正していきます。

=PHONETIC(INDEX(B5:B14,MATCH(B2,B5:B14,0)))

これで、フリガナを表示することができるようになります。

それでは、この数式を説明していきます。

最初のPHONETIC関数は、「フリガナ」情報を表示することができる関数ですね。

INDEX関数で指定した行列番号が交差するセル参照する関数ですね。

そして、INDEX関数と相性抜群のMATCH関数を組み合わせます。

MATCH(B2,B5:B14,0)

と数式を設定していますが、B2の値は、B5:B14のなかで、何番目にあるのかを算出することができます。

MATCH関数は、範囲内にある検索値の位置を算出する関数です。

このように、INDEX関数とMATCH関数を合わせることで、元にあるフリガナ情報を抽出することができます。

INDEX＋MATCH関数は、覚えておくといい、関数の組み合わせかもしれませんね。

Excel。列方向の重複を除いて、行方向の表で抽出するには、どうしたらいい【Remove duplicates】

Excel。列方向の重複を除いて、行方向の表で抽出するには、どうしたらいい

＜INDEX＋ROW関数＞

重複しているデータを除いた表を作る場合には、データタブの「重複の削除」をつかうと効率的に作成することができます。

ただし、この「重複の削除」は行方向。

つまりレコードが対象になっているので、列方向であるフィールド方向に重複したデータを除くのは、なかなか面倒な作業といえます。

次の表をつかって、やりたいことを説明していきます。

 

1行目のB1:G1のデータから重複したデータを除いたものを、A4を起点とした表をつくりたいというのが、やりたいことです。

列方向では、「重複の削除」をつかうことはできません。

そこで、先に、列方向のデータを行方向にする作業をしていきます。

列方向を行方向に変換するには、「INDEX関数」をつかうと、手早く処理することができます。

A4に次の数式を設定します。

=INDEX($B$1:$G$1,1,ROW(B1))

あとは、必要なデータだけオートフィルで数式をコピーします。

 

INDEX関数の説明は後述するとして、重複データの削除をおこなっていきます。

このまま、データタブの「重複の削除」をおこなってしまうと、一瞬成功したように思えるのですが、内容は数式なので、結果うまくいきません。

まずは、INDEX関数で行列を入れ替えたデータを数式から値として変更します。

A4:A9を範囲選択して、コピーしたら、そのまま貼り付けの「値」をクリックします。

あとは、データタブの「重複の削除」を実行したら、完成ですね。

それでは、INDEX関数を確認しておきましょう。

最初の引数「配列」には、$B$1:$G$1と設定します。

オートフィルで数式をコピーするので、絶対参照も忘れずに設定します。

2番目の引数の行番号は、行をずらすとかしないので、「1」と設定します。

3番目の引数の列番号は、「1」「2」「3」…と列参照をずらしていきたいわけですね。

そこで、ROW関数をつかうと、オートフィルで数式をコピーするのと連動して「1」「2」「3」…と数値を変えることができます。

そこで、ROW(B1)と設定しました。

なお、ROW(A1)でもOKです。

INDEX関数はアイディアによって色々活用することができますので、試してみるといいかもしれませんね。

Excel。行が等間隔のデータを列方向に簡単にコピーするにはどうしたいいの【Equally spaced】

Excel。行が等間隔のデータを列方向に簡単にコピーするにはどうしたいいの

＜INDEX+COLUMN関数＞

帳票の都合で、どうしても行方向ではなくて列方向に等間隔のデータをコピーするには、どのようにしたら簡単に、しかも手早く処理をすることができるのでしょうか？

例えば、次のような表の場合です。

四半期のデータが、B5・B9・B13と3行空けた等間隔にある表があります。

この表内の四半期のデータを列方向に抽出というかコピーをしたいわけです。

コピー&ペーストするだけですが、データ量が増えると面倒ですし、貼り付けで、行列をいれかえた貼り付けるやり方だと、余計なデータも含めてしまい、データの削除が面倒になってしまいます。

Excel VBAでわざわざプログラムをつくるのも、ちょっと面倒です。

このような場合、INDEX関数をつかった数式で、簡単に処理することができるので、その方法をご紹介します。

D2にINDEX関数の数式をつくっていきます。

=INDEX($B$2:$B$13,COLUMN(A1)*4)

この数式だけで、行の等間隔のデータを列方向に抽出することができます。

では、INDEX関数の引数を確認しておきましょう。

最初の引数は、配列。

データがある範囲なので、B2:B13が対象になります。

オートフィルで列方向に数式をコピーしますので、絶対参照を設定して、$B$2:$B$13。

2つ目の引数は、行番号です。

ポイントは、この行番号です。

どのようにしたら、等間隔に指定することができるのかを考える必要があります。

C列に連番を設定してみると、4の倍数であることがわかります。

オートフィルで列方向に移動した時に、最初が1で次が2というような数値を算出するものがあれば、この4倍というパターンがつかえそうです。

そこで登場するのが、COLUMN関数です。

COLUMN関数は、アクティブになっているセルの列番号を算出してくれる関数です。

数式を設定しているセルはD列なので、「4」と算出してしまうので、COLUMN(A1)とセル番地を設定することで、「1」と算出することができます。

オートフィルで列方向に数式をコピーすれば、COLUMN(B1)となり、「2」と算出してくれますので、4の倍数というパターンでつかうことができるわけです。

引数の列番号は、省略するので、あとは、オートフィルで数式をコピーすれば、完成というわけです。

VLOOKUP関数などでうまくできないケースをはじめ、INDEX関数が重宝するケースもありますので、色々試してみると数式の可読性も向上するかもしれませんね。

Excel。等間隔のデータを手早くコピーしてリスト化するにはどうしたらいいの？【Equally spaced】

Excel。等間隔のデータを手早くコピーしてリスト化するにはどうしたらいいの？

＜INDEX+ROW関数＞

セル参照にしても、コピーペーストをするとしても、等間隔のデータを手早くコピーしてリスト化する作業は結構面倒です。例えば次のような表の場合です。

３日ごとの集計されている合計値を、別セルにリスト化したいわけですね。

サンプルのように件数が少ない場合、「努力と根性」でどうにかできますが、面倒です。

Excel VBAでプログラムを作ってもいいですが、簡単な関数をつかった数式で対応することができます。

その数式は、

=INDEX($B$2:$B$13,ROW(A1)*4)

という数式が、E1に設定されています。

この数式を、オートフィルで数式をコピーするだけで、簡単で、手早くリスト化することができます。

今回のようなケースの場合は、行番号・列番号が交差されるセル番地を算出できる「INDEX関数」か、指定した参照の行番号・列番号の範囲の値を算出する「OFFSET関数」のどちらかで、対応できないかを考えてみるといいですね。

このデータの特徴としては、3日間の合計値は、行数が、4つごと。

つまり、4の倍数ごとに登場しているわけです。

そこで、行番号・列番号が交差されたセル番地を参照できる「INDEX関数」をつかうことで、算出できるのではと、想像します。

では、実際にINDEX関数をつかって、数式をつくってみます。

INDEX関数は、2つの種類から選択できる関数です。

INDEX（配列,行番号,[列番号]）で算出できますので、こちらを採用します。

引数を確認していきます。

配列ですが、これは、値がある範囲なので、$B$2:$B$13。

完成した数式をオートフィルでコピーしますので、絶対参照を設定しておきます。

行番号。

ROW(A1)*4

ここがポイントです。

合計値が登場しているセルは、B5・B9・B13ですが、配列で、B2:B13としています。

行数は2行目が1で13行目が12なわけですから、実際に求めたい数値は、「4・8・12」ですね。

B2:B13の４行目・8行目・12行目を算出したいわけです。

B5＝4行目＝1×4

B9＝8行目＝2×4

B13＝12行目=3×4

1～3と増加する数値は、行番号を算出することができるROW関数をつかうと、すべての問題をクリアすることができます。

ROW(A1)は1。オートフィルで数式をコピーするので、ROW(A2)なら2と算出してくれます。

引数の列番号は、列を移動させる必要がないので、省略が可能です。

INDEX関数は、アイディアによって使い勝手がいい関数なので、機会がありましたら、アレコレつかってみると、現場で使用している数式を、コンパクトにすることができるかもしれませんね。

Excel。結合したセルにデータをコピーするとズレるので、どうにかしたい【Merged cells】

Excel。結合したセルにデータをコピーするとズレるので、どうにかしたい

＜INDEX+ROW関数＞

結合したセルは、ちょっとクセというか困ったことがあります。

例えば、次のような場合。

A列に店舗名があって、C:D列のように、上半期下半期がある表に店舗名を設定したいわけです。

先に、C2:C3をセル結合しておいたとしても、A2:A5を単純にコピーして貼り付けても、セルの結合は解除されるし、店舗名も希望のように設定することができません。

やりたいのは、このような表にしたいわけです。

ですから、単純なコピーではダメですし、セル参照によるオートフィルで数式をコピーも意味を成しません。

次のような結果になってしまいます。

今回のように、4店舗程度ならば、自分で入力したほうが早いかもしれませんが、件数が増えてしまうと、とても大変な作業になってしまいます。

このようにセル結合した時には、ちょっとクセがあるというわけですね。

では、どのようにしたらいいのでしょうか？

C2には、このような数式を設定して、オートフィルで数式をコピーしてみましょう。

=INDEX($A$2:$A$5,ROW(A2)/2)

INDEX関数を使うことで、希望のように、店舗名を設定することができました。

ROW関数はつかっていますが、シンプルなINDEX関数だけで対応できるのです。

では、この数式を見ていきます。

最初の引数は、配列ですが、これは、範囲なので、$A$2:$A$5を設定します。

2つ目の引数は、行番号ですが、ここがポイント。ROW関数をつかっています。

このROW関数の意味を知るために、E列にROW関数の結果を算出してみました。

結合されたセルは、上側の行番号が算出されていることがわかります。

A2:A5までのデータにある行番号は1～4なので、ROW関数で算出した結果をオートフィルで数式をコピーしてしまうと、2/4/6…と2の倍数で増えてしまうので、2で除算することで、1～4の数値を算出することができます。

なので、INDEX関数の2つ目の引数である行番号には、ROW(A2)/2と設定しているわけです。

よって、C2のINDEX関数は、

=INDEX($A$2:$A$5,ROW(A2)/2)

とすることで、オートフィルで数式をコピーするだけで、結合したセルに、値を設定することができます。

INDEX関数は、色々使える関数ですので、色々試してみるといいかもしれませんね。

Excel。縦一列の氏名を、3列編成したいけど、コピぺじゃ大変なんです。【Configuration】

Excel。縦一列の氏名を、3列編成したいけど、コピぺじゃ大変なんです。

＜INDEX関数＞

やりたいことは単純でも、Excel VBAにするほどじゃないし、けど、コピー&ペーストじゃ面倒という作業は結構あります。

例えば、次のような縦一列の名簿

A1:C3の名簿のC列の氏名を、E1:G5にあるような、3列編成の表で表示するとしたら、いちいち、コピー&ペーストで処理しようとすると、簡単ではありますが、ホント大変です。

そこで、何か効率的な方法はないかということ、このような場合には、「INDEX関数」をつかうことで、一発解決します。

ただ、準備が必要になります。

E10:G13に、配置したい順番に、番号を振っていきます。

準備はこれだけです。

E2にINDEX関数をつかった数式を作っていきます。

INDEX関数は、手入力するほうが楽な関数なので、手入力で設定することをおススメします。

=INDEX($C$2:$C$13,E10)

あとは、3列編成になるように、オートフィルで数式をコピーしていきます。

これで、3列編成で表示することができました。

INDEX関数は、2種類ありますが、今回は、配列タイプで算出しています。

配列が、氏名の列なので、$C$2:$C$13。オートフィルで数式をコピーするので、絶対参照を忘れないようにしましょう。

行番号は、E10。

列番号は、氏名の1列だけなので、省略しました。

たった、これだけなんですね。ですが、時間短縮の効果抜群なのが、INDEX関数の特徴でもあります。

仕組みがわかれば、INDEX関数だけで、様々な編成した表にすることができます。

例えば、次のようなパターン

縦一列を、変則的な横二列編成の表で表示してみました。

上下方向のオートフィルで数式をコピーは、セル参照でも対応できるかもしれませんが、上下方向の表を横方向にするためには、オートフィルで数式をコピーというわけにはいきません。

しかし、INDEX関数をつかうことで、このような表に編成しなおすことも容易です。

ポイントは、行番号をどのようにしたらいいのかということです。

この表のように、1行目と4行目にある数値を行番号として使用することができれば、3列編成のときのように、配置するための別表を用意する必要はありません。

今回紹介したINDEX関数を知っていると、色々現場レベルでつかるかもしれませんので、機会がありましたら使ってみてはいかかでしょうか。

Excel。結合したセルの値をまとめた一覧表を作りたいけど、コピペじゃ大変なんです。【Merged cells】

Excel。結合したセルの値をまとめた一覧表を作りたいけど、コピペじゃ大変なんです。

＜ROW関数・IFERROR+INDEX+SMALL関数＞

セルの結合は設定するのが簡単なのですが、結合したセルの値を別のセルにコピーするとか、一覧表を作る場合、意外と大変というか、困ってしまうことがあります。

例えば、次の表のような処理をする場合で、説明していきます。

A列には、セル結合された担当者名が入力されています。

入力規則のリストなどで使うために、D列のように担当者一覧を作りたいとします。

今回は、4名なので、コピー&ペーストでも、どうにかなりますが、件数が多かったらコピー&ペーストでは大変です。

D2に「=A2」というセル参照を設定して、オートフィルで数式をコピーしても、できませんよね。

Excel VBAでマクロをつくってもいいのですが、マクロを作成すること自体も面倒です。

今回のような、結合セルの値から、一覧表をつくるには、ちょっとした関数を組み合わせれば、可能になります。

では、早速作っていきましょう。

C2に、次の数式を設定して、オートフィルで数式をコピーします。

=IF(A2<>"",ROW(A1),"")

このように、算出されます。

数式の説明をします。

縦方向にセルが結合されている場合、結合されている一番上のセルに値が設定されいます。

値が入っているか、どうかを判断したいので、IF関数を使います。

論理式は、「A2<>""」。

A2が空白ではなかったらと、問い合わせます。

真の場合は、「ROW(A1)」。

ROW関数で行番号をつかって、数値を算出させます。

義の場合は、空白　とします。

これによって、値があるセルの場所がわかりました。

続いて、D列にまとめる作業を行います。

D2に次の数式を設定します。

=IFERROR(INDEX($A$2:$A$10,SMALL($C$2:$C$10,ROW(A1))),"")

数式を説明していきます。

IFERROR関数を使うのは、このあと、オートフィルで数式をコピーすると、抽出データがないセルに、「#NUM!」というエラーが表示されるので、その防止のために使います。

INDEX関数をつかうことで、行番号と列番号が交差したデータを抽出することができます。

INDEX関数は、INDEX（参照,行番号,列番号）という引数をもっています。

参照は、担当者名を算出したいわけですから、$A$2:$A$10。

オートフィルで数式をコピーしますので、絶対参照を設定します。

行番号は、

SMALL($C$2:$C$10,ROW(A1))

SMALL関数は、その数値の順位の数値を算出します。

このために、先程のIF関数で算出したわけです。

SMALL関数は、SMALL（配列,順位）という引数をもっています。

配列は、IF関数で算出した範囲の、$C$2:$C$10

絶対参照も忘れずに設定します。

順位は、ROW(A1)　A1を指定することで、「1」という数値を算出することができます。

オートフィルで数式をコピーすると、A2・A3とかわることで「2」「3」とスライドすることができます。

列番号は、今回複数列ではないので、省略します。

すると、このような結果になりました。

このように、セル結合した値でも一覧表を作ることができました。

最後に、C列の数値を表示してあると、カッコ悪いので、非表示にしますので、C2:C10を範囲選択して、セルの書式設定ダイアログボックスを表示します。

表示形式の「ユーザー定義」にして、種類に「;;;」（セミコロン×3）と設定します。

これで、文字を非表示することができました。

セルの結合は、見た目としては、便利なのですが、そのあとに、何かしたい場合は、うまくいかないことがありますので、見た目を重視する必要がない場合は、データベースとして表を作る方がいいかと思います。

Excel関数辞典 VOL.40。IMSQRT関数～INDIRECT関数【dictionary】

Excel関数辞典　VOL.40。IMSQRT関数～INDIRECT関数

＜Excel関数＞

今回は、IMSQRT関数～INDIRECT関数までをご紹介しております。

IMSQRT関数

アイエムスクエアルート

複素数の平方根を算出する

IMSQRT（複素数）

IMSUB関数

アイエムサブ

複素数の差を算出する

IMSUB（複素数1,複素数2）

IMSUM関数

アイエムサム

複素数の和を算出する

IMSUM（複素数1[,複素数2]）

IMTAN関数

アイエムタンジェント

複素数のタンジェントを算出する

IMTAN（複素数）

INDEX関数

インデックス

セル範囲から縦横座標で値を抽出

INDEX（配列,行番号,[列番号]）

INDEX（参照,行番号,[列番号],[領域番号]）

INDIRECT関数

インダイレクト

文字列で参照されるセルの値を算出

INDIRECT（参照文字列,[参照形式]）

Excel。ガントチャートで期間の「始点～終点」を見出しから抽出してつくりたい。【Gantt chart】

Excel。ガントチャートで期間の「始点～終点」を見出しから抽出してつくりたい。

＜ガントチャート：INDEX+MATCH関数＞

スケジュール期間をわかりやすく管理するガントチャートという表があります。

作業Aは、6月1日に最初の○があるので、B列は、6/1～と表示され、6月3日に最後の○があるので、6/3と判断した結果、B2には、「6/1～6/3」と自動的に算出し表示しています。

今回は、この期間の作り方を紹介していきます。

どうしたら、最初の「○」がある日付を抽出することができるのかを考えていきましょう。

C1:G2までの表で考えるとわかりやすくなります。

日付がある行は、1行だけなので、行数は1で、列は、5列ある表。

つまり、1行5列の表の中から、最初の「○」がある6月1日を抽出させるには、6月1日は、1行1列のデータを抽出すればいいわけです。

このような、行と列から抽出する時には、INDEX関数を使うことで算出することができます。

問題は、最初の「○」が1列目で、最後の「○」は3列目にありますが、この1とか3をどうやって計算させればいいのでしょうか？

何番目に「○」があるのか？

このような時には、MATCH関数を使うことで算出することができます。

B2をクリックして、数式をつくっていきます。

INDEX関数は、2種類選べる関数になっているので、手入力の方がわかりやすいかと思いますので、手入力でつくることをお勧めします。

B2の数式は、
=INDEX($C$1:$G$1,1,MATCH("○",C2:G2,0))

2020/6/1という算出結果がでました。結果がシリアル値の場合は、日付型に変更しましょう。

この計算式について説明します。

INDEX関数は、INDEX(配列,行番号,列番号)という引数をもっていますので、
配列には、$C$1:$G$1　を設定します。

オートフィルで数式をコピーすることを考慮して、絶対参照も合わせて設定しておきます。

行番号は、1行なので、1。
列番号は、最初の「○」がある場所の数値ですが、この数値をMATCH関数で算出します。

MATCH関数を確認します。
MATCH関数は、MATCH(検索値,検索範囲[,照合の種類])という引数を持っています。

検索値には、「○」。
検索範囲には、C2:G2。

照合の種類ですが、0を設定します。0は、完全一致する値を算出します。

また、最初に登場する値を算出することができます。

よって、0を設定することで、「1」を得ることができるというわけです。

なお、「0」の代わりに「-1」にすると、検索値の中で最小値を算出してくれるので、「-1」でもかまいません。

このように、INDEX関数とMATCH関数を組み合わせることで、抽出することができます。

この数式を、「～」で挟む数式に修正していきます。

=INDEX($C$1:$G$1,1,MATCH("○",C2:G2,0))&"～"&INDEX($C$1:$G$1,1,MATCH("○",C2:G2,1))

ちょっと、長くなりましたが、「&"～"&」で始点の見出しの日付から終点の見出しの日付までという形で表示することができます。

ただ、表示形式が解けてしまうので、シリアル値で表示されてしまっています。

日付に表示形式を変更したいのですが、文字結合していることもあって、通常の表示形式の変更ではかわりませんので、TEXT関数をつかってあげる必要があります。

よって、最終的な数式は、
=TEXT(INDEX($C$1:$G$1,1,MATCH("○",C2:G2,0)),"m/d")&"～"&TEXT(INDEX($C$1:$G$1,1,MATCH("○",C2:G2,1)),"m/d")

とさらに長くなってしまいましたが、オートフィルで数式をコピーして完成です。

ちょっと長い数式ではありますが、作業工程に変化があった場合でも、この表の場合だと、「○」を追加削除するだけで、期間がオートマチックに連動して変更することができるので、ミスが抑制できるかもしれませんね。

Excel。XLOOKUP関数だと簡単！降順の表から検索するならINDEX+MATCH関数の苦労から解放?!【XLOOKUP】

Excel。XLOOKUP関数だと簡単！降順の表から検索するならINDEX+MATCH関数の苦労から解放?!

＜INDEX+MATCH関数・XLOOKUP関数＞

次のような合計値に応じてランクを決定する表があります。

数値をもとにして、ランクの表から検索するので、「VLOOKUP関数」を使うように思われますが、このケース。

VLOOKUP関数では、対応することができません。

C2:C10を一度クリアして、VLOOKUP関数で算出してみましょう。

C2には、
=VLOOKUP(B2,$F$2:$G$7,1,TRUE)
という数式になるはずです。

しかし、「#N/A」というエラーが表示されてしまいます。

検索値は、B2だし、範囲も問題ない。
抽出したいのは、ランクなので、列番号は「1」。
完全一致ではなく、近似値なので、「TRUE」と大丈夫なような気がしますが、VLOOKUP関数のルールを逸脱しています。

１つ目は、検索値の左側に抽出したいデータがある。
今回はG列の合計の左にランクがある。
抽出したいものが検索値の右側にないとVLOOKUP関数を使うことができません。

では、F列とG列を入れ替えればいいのかというと…

C2の数式も合わせて次のように修正します。

=VLOOKUP(B2,$F$2:$G$7,2,TRUE)
オートフィルを使って数式をコピーしたところ、エラーのセルがあります。

実は、まだVLOOKUP関数のルールから逸脱しています。

2つ目として、範囲のF2:G7の表ですが、今回のような数値の場合は、【昇順】でなければいけません。

商品コードや氏名の場合は、ルール上よろしくありませんが、完全一致のケースが多く、検索されます。

なので、次のようにF列を昇順にすると、問題なく検索してくれます。

しかしながら、通常数値の高い順でリストをつくるだろうし、Sが何点以上というように表を閲覧するはずです。

よって、最初の表では、VLOOKUP関数を使うことはできないわけです。

では、どのようにしたらいいのかというと、このケースでの定番「INDEX+MATCH関数」をつかうことで、検索することができます。

さらに、F1:G7の表だけではINDEX+MATCH関数でも、検索することはできません。

H列の「～まで」という数値を用意する必要があります。

B2に、INDEX+MATCH関数で数式を作ってみましょう。

=INDEX($F$2:$F$7,MATCH(B2,$H$2:$H$7,-1),1)

これで、検索することができるのですが、数式が非常に「難解」ですね。

数式の説明をしてきます。

MATCH関数がどのような動きをしているのか確認してみましょう。

D2の数式は、
=MATCH(B2,$I$2:$I$7,-1)
検索値は、B2で198を指します。

検索範囲は、$I$2:$I$7
照合は、「-1」とします。「-1」は、検索値以上の最小値を検索します。

すると、上から3コ目が該当するので、3と算出されました。

それを受けて、E2のINDEX関数を確認しましょう。
=INDEX($G$2:$G$7,D2)
G2:G7の範囲で、上から、D2、つまり、3番目を検索するので、「B」を検索してくれるわけです。

このように、検索する表をそのまま使用して、検索することは容易ではありませんでした。

しかし、Office365のExcel　 Insiderで搭載された、「スピル」そして、新しく登場した【XLOOKUP関数】をつかうと、INDEX+MATCH関数のような苦労はせずにすむようになりました。

D2にXLOOKUP関数の計算式をつくっていきます。

=XLOOKUP(B2:B10,G2:G7,F2:F7,,-1,1)
これで、あっという間に算出してくれます。

範囲の表が、どうのこうのというような条件はありません。

XLOOKUP関数は、スピル機能を使う関数なので、オートフィルを使って数式をコピーする必要はありません。

また、絶対参照で範囲の表を固定する必要もありません。

それでは、数式を説明しましょう。

まず、XLOOKUP関数は、
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
という形式になっています。

検索値は、B2:B10。
合計点がどのランクなのか検索するので、B2:B10と設定します。

検索範囲は、G2:G7。検索値に該当するのを検索する範囲です。

戻り範囲は、F2:F7。検索結果の範囲です。

このように、検索範囲と戻り範囲を別々に設定できるようになったので、検索範囲を範囲の表の一列目にないといけないというルールから解放されたわけです。

見つからない場合は、今回はないので、省略。

一致モードは、完全一致かどうか決めるところです。
VLOOKUP関数では省略すると、「近似値」になってしまいましたが、XLOOKUP関数では、省略すると「完全一致」になります。

今回は、完全一致または、次に小さい項目の「-1」を設定します。

1件目は198なので、198の次に小さい項目は、190が該当しますので、Bを検索するわけです。

最後の、
検索モードは、リストの上から検索させるので、先頭から末尾へ検索の「1」を選択します。

この検索モードによって、VLOOKUP関数だと、範囲の表を昇順にしておかないといけないというルールから解放されました。

このように、XLOOKUP関数は、現場で大きなインパクトを与える関数になるのかもしれませんね。