エクセルで作られた社員名簿のデータを別シートにINDEX関数を使って抜き出したいのですが、MATCH関数での列指定が上手くいきません。
表は
   A    B     C     D     E     F
1 社員番号 社員氏名A 家族1氏名 家族1年齢 家族2指名 家族年齢
2 社員番号 社員氏名B 家族1氏名 家族1年齢 家族2指名 家族年齢
3 社員番号 社員氏名C 家族1氏名 家族1年齢 家族2指名 家族年齢
と、社員1名につきその家族が一行で表示されています。
これを別シートに家族1名につき一行の表があるので、年齢だけをこの表から持っていきたいのですが、一行に複数の家族がいるので、MATCH関数での列指定が上手くいきません。何かいい方法はないでしょうか?

このQ&Aに関連する最新のQ&A

A 回答 (2件)

#01です


OFFSET関数は基準セルから行数、列数を指定して離れたセルを参照しますが、基準セルがOFFSET(A1,0,0)、右となりならOFFSET(A1,0,1)になります。C1を指すときはOFFSET(A1,0,2)です

一方MATCH関数で列方向の検索をする場合はA列を基準(1)として何列目かを返します。C列に目的の検索語があれば3が返ります。
MATCHで得た3をOFFSET関数で使うために -1 しています。

今回はOFFSET(基準セル、行位置、列位置、行数、列数)の型も使用しています。OFFSET関数のHELPを見て、意味を考えて見て下さい

20は「20列目まで」を範囲とするために指定しましたが、別に40でも100でもかまいません。20を指定したので(家族氏名、年齢)の対は9個までと書いたのです(最初の2列は社員番号、社員氏名)
    • good
    • 0

シート1が以下の時に


    A列   B列    C列    D列  E列    F列
1行目 123456 社員氏名A  家族A1  40  家族A2  18
2行目 234567 社員氏名B  家族B1  35  家族B2  9
3行目 345678 社員氏名C  家族C1  29  

シート2に以下の結果を得るなら
    A列   B列    C列    D列
1行目 123456 社員氏名A  家族A1  40
2行目 123456 社員氏名A  家族A2  18
3行目 234567 社員氏名B  家族B1  35
4行目 234567 社員氏名B  家族B2  9
5行目 345678 社員氏名C  家族C1  29  

シート2のD1の式は以下になります
=OFFSET(Sheet1!$A$1,MATCH($A1,Sheet1!A:A,0)-1,MATCH($C1,OFFSET(Sheet1!$A$1,MATCH($A1,Sheet1!A:A,0)-1,0,1,20),0))

D1を下方向にコピーしてください。なお扶養家族は9人までOKです

この回答への補足

早速の回答ありがとうございます。こんな関数があったとは知りませんでした。勉強になりました。
ちなみに質問なんですが・・・
この式で-1や20という数字は何を意味しているのでしょうか?私にはちょっと複雑すぎて式の意味がいまいちよく理解できません^^;

補足日時:2007/09/26 15:56
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q【エクセル】A列で英語、B列で日本語を入力する方法

皆さん
英単語のリストをするため、A列に英語、B列に日本語を打つという作業を何行も繰り返すのですが、半角/全角をいちいち変更するのが大変です。そこで、自動的に、文字入力をスイッチする方法はないでしょうか?

ご回答よろしくお願いいたします。

Aベストアンサー

こんばんは。

そういう場合は<入力規則>を使います。

次のようにしてください。
------------------------
列記号のAをクリックして、A列をすべて選択
  ↓
メニュー<データ>-<入力規則>の"日本語入力"タブ
  ↓
"日本語入力" で "オフ(英語モード)" を選択してOK
  ↓
B列をすべて選択
  ↓
同様にして "日本語入力" で "ひらがな" を選択してOK
  ↓
以上で、自動的に入力モードが切り替わります。

QINDEXとMATCHで別シートの関数を引く方法

定義シートに支払方法のリストが作ってあり、データベースとなる入力シートで支払い方法をA列にリストから選択で入力、B列に計上日を入力すると、C列に支払方法に合わせた支払い予定日が自動で入るようにしたいと思い、以下のようにしました。
入力シート B列の$B$2セル以下は「計上日」という名前を定義。
      C列には=INDEX(支払予定日関数,MATCH(A2,支払方法,0),2)を設定し、定義シートB列の関数を引いて来る

定義シート 下記の$A$2:$B$4には「支払予定日関数」の名前を定義
      $A$2:$A$4には「支払方法」の名前を定義
      ほかに祝日一覧を作り「祝日」の名前を付けてあります。
       A列      B列
     2 現金     =計上日
     3 普通預金   =計上日
     4 カード    =IF(DAY(計上日)<16,WORKDAY(WORKDAY(DATE(YEAR(計上日),MONTH(計上日)+1,10),-1,祝日),1,祝日),WORKDAY(WORKDAY(DATE(YEAR(計上日),MONTH(計上日)+2,10),-1,祝日),1,祝日))
ところが、うまくいきません。「支払い予定日関数」の行列を入れ替えたり、B列の関数の前に「”」や「’」をつけてみたりしたけど、たとえば、6行目の計上日の答えをほしいのに、カードであれば、計上日列トップから数えて3行目の計上日に対応する日付が返るのです。「支払い予定日関数」でカードが3行目だからですね。何を直したらいいのでしょうか?

定義シートに支払方法のリストが作ってあり、データベースとなる入力シートで支払い方法をA列にリストから選択で入力、B列に計上日を入力すると、C列に支払方法に合わせた支払い予定日が自動で入るようにしたいと思い、以下のようにしました。
入力シート B列の$B$2セル以下は「計上日」という名前を定義。
      C列には=INDEX(支払予定日関数,MATCH(A2,支払方法,0),2)を設定し、定義シートB列の関数を引いて来る

定義シート 下記の$A$2:$B$4には「支払予定日関数」の名前を定義
      $A$2:...続きを読む

Aベストアンサー

No.2,3,4です。
「○○日締め翌○○日払い」がいろいろあるのでしょうか。
だとすると、表にすべきはこの日付ですね。

「支払予定日」(名前定義します)
カード名 締め日 支払日
カードA 15 10
カードB 25 20
・・・

=IF(OR(A2="現金",A2="普通預金"),B2,WORKDAY(EOMONTH(B2-VLOOKUP(A2,支払予定日,2,0),1)+VLOOKUP(A2,支払予定日,3,0)-1,1))

条件に応じて工夫してみてください。

Qエクセルの印刷タイトルの行と列を同時に設定するには?

エクセルについて教えてください。
セルA1に「学科別2学期得点表」と入力します。
A1の列幅より言葉はとびでています。
次にA2に「英語」A3に「体育」と入力します。
以上の内容を毎ページに自動で表示させたいと
思っています。
ページ設定で「行のタイトル」を『$1:$1』
「列のタイトル」を『$A:$A』としました。
エクセルのデータは縦に下へ入力するのではなく
右へどんどん入力しています。
この入力の方法でこの設定ですと、列のタイトルのみが自動で表示され、行のタイトルが自動表示されません。
でも列と行のタイトルの両方を同時に設定できるので両方とも自動表示されていいと思うのですが・・・
お分かりになる方回答をお願いします。
入力はこんな感じです。


  A   B   C   D   E  ・・・  


1 学科別2学期得点表 

2 英語 34   34   77  89  ・・・

3 体育 23   33   33  99  ・・・

 データは、得点の入力のみで右側へどんどん入力していきます。

よろしくお願いします。

エクセルについて教えてください。
セルA1に「学科別2学期得点表」と入力します。
A1の列幅より言葉はとびでています。
次にA2に「英語」A3に「体育」と入力します。
以上の内容を毎ページに自動で表示させたいと
思っています。
ページ設定で「行のタイトル」を『$1:$1』
「列のタイトル」を『$A:$A』としました。
エクセルのデータは縦に下へ入力するのではなく
右へどんどん入力しています。
この入力の方法でこの設定ですと、列のタイトルのみが自動で表示され、行のタイトルが自動表示さ...続きを読む

Aベストアンサー

まず「行のタイトル」の意味を間違って理解されているようです。
「行のタイトル」は、縦方向の改ページの際に、毎ページに印刷されるという機能です。今回の場合、横方向に長い表で、横方向の改ページとなるようですから、「列のタイトル」だけが有効になります。このため、列のタイトルである「A」列の列幅に収まる内容しか印刷されません。このため、どうしても「列のタイトル」だけで済ませるならば、「学科別2学期得点表」というタイトルの文字をA列の列幅に収まるようにフォントサイズを小さくする(列幅を多少広げた方がいいでしょうが)、もしくは、タイトルを「学科別」「2学期」「得点表」のように3行にするなどの対処法があるでしょう。

そうでなければ、ほかの方の回答にあるように「ヘッダー」にこのタイトルを指定することです。別にA1セルには、そのままタイトル文字を入力しておけばいいので、表示上は問題ないでしょう。ただ、この1行目を含めないように印刷範囲を設定することと、「行のタイトル」の設定もクリアしておくことを気をつけてください。

Qエクセルの関数です。一列目で指定した値の間で、二列目で指定した値を示す、一列目の最初の値を求める。

エクセルの関数です。
一列目で指定した値の間で、二列目で指定した値を示す、一列目の最初の値を求める関数を教えてください。
添付した図で、具体的に説明します。
A列に値(時間)、B列に値があります。
この配列の中から、
F4の値(時間)と同じ値(時間)を示すA列の行から、F5の値(時間)と同じ値(時間)を示すA列の行までの中で、
F3の値と同じ値がB列にある、A列の値(時間)の内、
A列で上から最初の値(時間)
です。

min、offset、index、match を組み合わせてみるのですが、うまくいきません。
どうぞよろしくお願いします。

Aベストアンサー

こんにちは!

画像の配置でF6セルに「7」という結果が返れば良い訳ですかね?

少し長くなりますが、
=INDEX(INDIRECT("A"&MATCH(F4,A:A,0)&":A"&MATCH(F5,A:A,0)),MATCH(F3,INDIRECT("B"&MATCH(F4,A:A,0)&":B"&MATCH(F5,A:A,0)),0))
という数式を入れてみてください。

※ F3セルは質問に載っていないので余計なお世話かもしれませんが
同じようなやり方で
=MAX(INDIRECT("B"&MATCH(F1,A:A,0)&":B"&MATCH(F2,A:A,0)))
という数式になると思います。

※ エラー処理はしていません。m(_ _)m

Qエクセルの印刷で途中の列を抜かしたい

エクセルの印刷で、A列、B列のとなりに、X列、Y列をくっつけて印刷したいのですが、どうすればよいでしょうか。
印刷した紙の上では、A列、B列、X列、Y列と4列が表示され、エクセルのファイル上では、C列からもちゃんとある状態です。
よろしくお願いします。

Aベストアンサー

不要な列は非表示にすれば良いと思います。

Ctrlキーを押しながら不要な列をクリックで選択。
2003ですと、書式→列→表示しない。

必要になれば隠れた列の左右の列を含み選択。
書式→列→再表示

ではいかがでしょうか。

QINDEX関数とMATCH関数を使っての数式

順位 氏名 Pt  
一位 武田  5     
二位 田中 8   
三位 町田 10     
四位 荒井 9       
五位 阿部 7

順位   氏名
一位
二位
三位


上の氏名の欄にINDEXとMATCH関数を使って一位から
三位の方の氏名を表示させたいのですがわかりません。教えてください。。。宜しくお願いします。

Aベストアンサー

A    B
1 順位 氏名   
2 一位 武田      
3 二位 田中   
4 三位 町田      
5 四位 荒井       
6 五位 阿部
7
8 順位 氏名
9 一位
10二位
11三位

としてB9に、
=INDEX($B$2:$B$6,MATCH(A9,$A$2:$A$6,0),1)
を入れて下へオートフィルでどうでしょう。

Qエクセルの表をワードに貼り付けると列幅が変わったりする問題の解決方法

エクセルで作った表をワードに貼り付けると入りきらなくて枠からでたり、
エクセルでは1列で書いた文字が貼り付けると2列になったり、列、行の幅大きく変わったりと困っています。

図として貼り付けるとエクセルで書いた表の通りにキレイ貼り付けられます。

これを図としてではなくエクセルで書いた通りの表で貼り付ける方法ありますか?
それとも絶対おかしくなるものなのでしょうか?
よろしくお願い致します。

Aベストアンサー

>直接とはエクセルを呼び出さず、ワードで表を書くような感じです。
これは、「リンク貼り付け」しない、という事でしょうか。「リンク貼り付け」を選ばなければ、エクセルから貼り付け後、ワード上でエクセルを直接起動せずに、エクセル表を編集できます。

>エクセルの表をコピーして貼り付け感じです。
少なくとも、エクセルの表をコピーする際にエクセルを呼び出す必要があると思うのですが。

>そうすると、表のどの列、行も選択できて編集できることを言ってました。
たぶん、その言ってた方に聞くのが一番早いと思います。
この文章を私の解釈からすると、私の回答でそのような事が出来ていると思われます。

出来ていないと思われるなら、何が出来て、何が出来ていないのかをもう一度整理して質問し直したほうがいいかもしれません。
お役に立てず、申し訳御座いません。

Q【Excel】複数の列を参照して、特定の文字列を含む行全体を別シートに抽出する関数

左画面(Book2)のようなシートがあります。

Book2の中で列D・列F・列Iの3列を参照し、別シート(Book4)のA1セルに入力した文字列と一致する文字を含む行全体を、Book4のA3セル以下に抽出するための関数を探しています。

画像の例は、「北海道」という文字列を3列のいずれかに含む行だけを手動で貼り付けたものなのですが、この処理を関数を用いて自動で行いたいのです。

参照したい文字列(北海道)は、3つの列にランダムに含まれている状態です。

元データ(Book2)を更新すれば、別シート(Book4)がリアルタイムに自動で生成されるようにしたいので、関数を使った方法で行いたいと思っています。

ご教示お願い申し上げます。

Aベストアンサー

こんばんは!
一例です。
元データは「Book2」のSheet1にあり、表示するのは「Book4」のSheet1とします。
Book4・Sheet1のA1セルに検索したい文字列を入力し、そのデータを表示させます。
(今回は画像通り”北海道”と入力しておいてください)

Book2のSheet1のJ列を作業用の列として使用します。
J3セルに
=IF(COUNTIF(D3:H3,[Book4.xlsx]Sheet1!$A$1),ROW(),"")
という数式を入れフィルハンドルでこれ以上データはない!という位まで下へコピーしておきます。

そしてBook4、Sheet1のA4セルに
=IFERROR(INDEX([Book2.xlsx]Sheet1!A:A,SMALL([Book2.xlsx]Sheet1!$J:$J,ROW(A1))),"")
という数式を入れ、列・行方向にフィルハンドルでコピーしてみてください。

※ 作業列が目障りであれば遠く離れた列に設けるか
非表示にしておきます。m(_ _)m

Qエクセル 複数列の入れ替えについて

エクセルで、下のような動きをさせたいとき使えるエクセル機能はありませんでしょうか?会社で、毎日インターネットからCSVでデータを落として、フォーマットの違うアプリケーションに取り込むため、毎日エクセル上で80列くらいを切り取り貼りしなくてはならず、エクセルで勉強したら使える機能があるのか、手作業しなければならないのか、またやり方を教えていだだきたいです。
実際はインターネットからのCSVで80列ぐらいのデータですが8列として、取り込めるアプリでは10列として例を書きます。
例)<インターネットから落としたCSVの並び順>
A列:受付No.、B列:会社名、C列:会社住所 D列:会社住所2 E列:担当者名 F列:電話番号 G列:ブランク H列:クレーム内容
<クレームを管理するアプリケーションで取込できるCSVの並び>
A列:受付No.、B列:ブランク、C列:クレーム内容、D列:担当者名、E列:会社名、F列:ブランク、G列:会社住所、H列ブランク、I列:会社住所2、J列:ブランク

毎日同じ並び替え作業をしていますが、これだけで半日以上つぶれてしまいます。
なにかいい方法がありましたら、どうぞよろしくお願いします。

エクセルで、下のような動きをさせたいとき使えるエクセル機能はありませんでしょうか?会社で、毎日インターネットからCSVでデータを落として、フォーマットの違うアプリケーションに取り込むため、毎日エクセル上で80列くらいを切り取り貼りしなくてはならず、エクセルで勉強したら使える機能があるのか、手作業しなければならないのか、またやり方を教えていだだきたいです。
実際はインターネットからのCSVで80列ぐらいのデータですが8列として、取り込めるアプリでは10列として例を書きます。
...続きを読む

Aベストアンサー

データの入れ替えだけですよね?
毎日なさっているのであれば、自動化できれば、大いに効率アップということになりますね。
たいして難しくないと思われますので、是非トライしてください。

◆方法1(入替えの雛形を作る)
シート1にCSVを取り込むと仮定して、事前に、シート2に入替えた形でシート1を参照する式を入れたものを用意しておく。
そうすればシート1にデータを取り込めば、自動的にシート2に必要なものができていることになります。

最終的にどのような形で利用するのか不明ですが、値として取得したければ、シート2をコピーし別シートに「形式を選択してペースト」-「値」で値のデータが得られます。


◆方法2(VBAを利用する)
単純な列の移動だけみたいなので、「マクロの自動記録」でもほとんど作成可能ですが、入替えのマクロを作成し、ボタンに登録しておくなどすれば、次回からはワンクリックで作業が完了するようになります。

QMATCH関数とINDEX関数の組み合わせ方

あるシートから別ファイルのシートへ、"金額の値"をコピーしたいのですが、
条件セルが元データでは縦2行(地域と商品名)、コピー先では(列)に配置されているため、
どうやって関数を組んだら良いのかわかりません。
 関数ではなく、マクロにしないと無理でしょうか?
(マクロでもいいのですが、コードがよくわからないので…)

 又、地域はコードで番号順に並んでいますが、商品名はアルファベットと数字が混ざっており、
ソートされていない並び(変更不可)になっています。

Sheet1(元データ)
   A      B    C
1 地域   商品名  金額
2 (01関東) (AS01) (200)
3 (01関東) (333B) (250)

このSheet1の金額データを別ファイル:Sheet2の該当欄に表示させたい。

Sheet2(コピー先)
  A           B     C      D
1 地域\ 商品名 (AS0100) (9999B) (AF205)
2 (01関東)       (200)   (300)   (250)
3 (02中部)       (50)    (250)    (290)

どなたか、お知恵を拝借できれば幸いです。

あるシートから別ファイルのシートへ、"金額の値"をコピーしたいのですが、
条件セルが元データでは縦2行(地域と商品名)、コピー先では(列)に配置されているため、
どうやって関数を組んだら良いのかわかりません。
 関数ではなく、マクロにしないと無理でしょうか?
(マクロでもいいのですが、コードがよくわからないので…)

 又、地域はコードで番号順に並んでいますが、商品名はアルファベットと数字が混ざっており、
ソートされていない並び(変更不可)になっています。

Sheet1(元データ)
...続きを読む

Aベストアンサー

Sheet2!B2: =IF(SUMPRODUCT((Sheet1!$A$2:$A$10=$A2)*(Sheet1!$B$2:$B$10=B$1)),INDEX(Sheet1!$C$2:$C$9,SUMPRODUCT((Sheet1!$A$2:$A$10=$A2)*(Sheet1!$B$2:$B$10=B$1),ROW(A$2:A$10)-1)),"")


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング