マンガでよめる痔のこと・薬のこと

シート(1)に番号、会社名、電話番号の一覧を作成していますが、
シート(2)へ番号を入力すると、自動で文字が入力できる方法を探しています。

ちなみに、1列からの検索でVLOOKUPを使う方法は分かるのですが、
今回は、複数の離れた列から検索を掛けたい場合はどの様な関数が使えるのでしょうか。
具体的には、別紙の通りです。

宜しくお願いします。

「EXCEL関数:複数の離れた列から抽出す」の質問画像

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

A 回答 (6件)

こんにちは!


すでに色々回答が出ていますが・・・
一例です。

↓の画像で上側がSheet1でSheet2に表示するようにします。
Sheet1はA~I列までのデータとしていますので、実状に合わせて範囲は変更してみてください。

Sheet2のB2セルに
=IF($A2="","",IF(COUNTIF(Sheet1!$A:$I,$A2),INDEX(Sheet1!$A$1:$I$100,SUMPRODUCT((Sheet1!$A$1:$I$100=$A2)*ROW($A$1:$A$100)),SUMPRODUCT((Sheet1!$A$1:$I$100=$A2)*COLUMN($A$1:$I$1))+COLUMN(A1)),"該当データなし"))

という数式を入れ、列・行方向にオートフィルでコピーすると
画像のような感じになります。m(_ _)m
「EXCEL関数:複数の離れた列から抽出す」の回答画像5
    • good
    • 0
この回答へのお礼

回答有難う御座います。
色々試してみます。

お礼日時:2013/08/06 15:54

質問文の画像のとおりに 0 番台、100 番台、200 番台、…というふうに番号の列が分かれている場合は、次式でもいいですね。

INDEX 関数の第 4 引数は、領域を番号で選択します。No.2 さんの CHOOSE と似たようなものです。

=vlookup(a2,index((sheet1!a:c,sheet1!d:f,sheet1!g:i),,,int(a2/100)+1),3,)

もっとも、いちばん一般的な方法は、No.4 さんのおっしゃるとおり、検索の元データを 3 列にすることだと思います。人に見せるなど、何らかの理由で 9 列にしたいなら、9 列の表のシートを 3 列のシートとは別に用意し、3 列から 9 列に参照する VLOOKUP を 9 列のセルに記入すればいいだけですね。
    • good
    • 0
この回答へのお礼

回答有難う御座います。
色々試してみます。

お礼日時:2013/08/06 15:54

複雑な操作がしたくないなら、入力・確認用にだけシート(1)の様に作成して、検索用にABC列だけのシートを作るのがもっとも簡単。


関数の使い方では無いですが・・・
    • good
    • 0
この回答へのお礼

回答有難う御座います。
確かにABC列だけが簡単ですが、
数がかなり有る為、3列での作業は厳しく…。
色々試してみます。

お礼日時:2013/08/06 16:00

「知っている(であろう)関数だけでわかりやすく」を求めるなら、


例えば「会社名」欄(シート2のB2セル)なら
  =IF(COUNTIF(シート1!A:A,A2)=0,"",VLOOKUP(A2,シート1!$A:$C,3,FALSE))&
   IF(COUNTIF(シート1!D:D,A2)=0,"",VLOOKUP(A2,シート1!$D:$F,3,FALSE))&
   IF(COUNTIF(シート1!G:G,A2)=0,"",VLOOKUP(A2,シート1!$G:$I,3,FALSE))
   ※便宜上、改行を入れていますが、実際は改行せずに続けて入力しましょう。

INDEX関数やMATCH関数を使って考えるのも手段ですが、
現在、ご自身が知っている関数だけで作るのもまた有効な手段です。
そちらの方が「あとでわかりやすい」でしょうから。


参考までにどうぞ。




好みの問題もあり、余計なお世話と言われるかもしれませんが、
シート名に「括弧や演算記号など」あるいは「丸数字のような環境依存文字」は
使わないようにしておく方が良いですよ。
前者は式が読みづらくなってしまうから、
後者はパソコンが変わると上手く動かなくなる可能性があるから、です。
    • good
    • 0
この回答へのお礼

回答有難う御座います。
色々試してみます。

お礼日時:2013/08/06 15:55

ご利用のエクセルのバージョンが不明のご相談なので、Excel2007以降を使い


=IFERROR(VLOOKUP(A2,Sheet1!A:C,2,FALSE),"")&IFERROR(VLOOKUP(A2,Sheet1!D:F,2,FALSE),"")&IFERROR(VLOOKUP(A2,Sheet1!G:I,2,FALSE),"")
のように計算するのが一番わかりやすい方法です。


ちなみにご相談は「例えば」でしょうからダメなんでしょうけど、仮に「実際にそうである」なら
=VLOOKUP(A2,CHOOSE(INT(A2/100)+1,Sheet1!A:C,Sheet1!D:F,Sheet1!G:I),2,FALSE)
と言った具合に、一つにまとめる工夫ができるかも?しれません。
    • good
    • 0
この回答へのお礼

回答有難う御座います。
色々試してみます。

お礼日時:2013/08/06 15:56

横に離れているなら普通にVLOOKUP関数でOK。


検索する列を変えれば良いだけです。

検索する範囲をOFFSET関数で指定して列方向をCOLUMNS関数で指定するとか工夫してみましょう。
    • good
    • 0
この回答へのお礼

回答有難う御座います。
色々試してみます。

お礼日時:2013/08/06 15:56

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

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

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

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

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

QVLOOKUP関数の範囲に、複数の範囲を指定したい

VLOOKUP関数の範囲に、複数の範囲を指定したいのですが方法がわかりません。
例えば同じシート内のC3:L8の範囲とM3:V8の範囲とW13:AF18の範囲を同時に
検索範囲として指定したいと考えております。

選択範囲に名前をつけて定義し、それを範囲として指定してみても、
なかなか上手くいきません。

効果的な方法があれば、御教鞭頂きたく思います。

Aベストアンサー

ごめんなさい。手抜きですね。
以下が条件です。

検索値がA1にある
検索範囲の中で検索値に合致する値が複数ない

=IF(ISERROR(VLOOKUP(A1,C3:L8,2,0)),IF(ISERROR(VLOOKUP(A1,M3:V8,2,0)),IF(ISERROR(VLOOKUP(A1,W13:AF18,2,0)),"",VLOOKUP(A1,W13:AF18,2,0)),VLOOKUP(A1,M3:V8,2,0)),VLOOKUP(A1,C3:L8,2,0))

最初の範囲で検索値に一致する物がなければ、次の範囲を探します。
その繰り返しです。
最終的にどこにもなければ空白です。

返す値の列番号は範囲の中で左から2番目です。
たとえば最初の範囲なら、D列の値が帰ります。

Qエクセルで離れた列を選択してグループ化出来ますか?

こんにちは。
エクセル表で、A~C列とE~G列をグループ化して、
表示や非表示を同時にしたいのですが、
それらの列を選択してグループ化しようとすると「複数の選択範囲に対してそのコマンドを使用することは出来ません」と出ます。
グループ化出来ないのでしょうか? それとも他に、右クリックの「表示しない、再表示」の他に、グループ化に匹敵した方法はありますか?
よろしくお願い致します。

Aベストアンサー

マクロ使うのは、大変ですようね。次のようにすれば表示や非表示を同時にすることが出来ます。

先ず、A~C列を「データ」→「グループ化・・・」→「グループ化」します。
次に、同様にE~G列をグループ化します。
すると、表の左上に「I」と「2」のボタンが表示されます。
「I」を押せば非表示、「2」を押せば表示が同時に出来ます.
お試し下さい。

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

QCOUNTIF関数 離れたセルを選択したい

A列に
10
10
11
11
12
12

がはいっていて、
1行目、3行目、5行目が10以上の数を数えたいのですが
=COUNTIF(A1,A3,A5,">10")
のように離れたセルを選択することは出来ないのでしょうか?

エラーになるようです。
COUNTIF関数じゃなければ可能ですか?

Aベストアンサー

No.1 さん、No.2 さんのご回答で、正しい答えが出ます。No.3 さんの数式については、名前を付ける範囲が連続したセル範囲である場合には、COUNTIF 関数が正しく機能します。

そのほか、作業列を使って答えを出す方法もご紹介します。添付図では、次式を記入しています。

B1 1
C1 =(a1>10)*b1
C8 =sum(c1:c6)

1 行おきに「1」を記入する方法の一例。B1 セルに「1」を記入した後、B2 は空白のままの状態で、B1:B2 のセル範囲を選択。次いで、キーボードの Ctrl キーを押しながら右下隅の角を下方向にドラッグすることで、1 行おきに記入されます。

なお COUNTIF の仕様では、第 2 引数までしかありません。したがって質問文のように、第 4 引数まで持たせた数式を書くことはできません。定められた以外の方法で関数を使っても正しい答えが出ないというのは、ごく当然の結果です。

Qエクセルの複数条件に一致したセルを抽出したいのですが、関数で教えてくだ

エクセルの複数条件に一致したセルを抽出したいのですが、関数で教えてください。

               A列   B列    C列
Aファイルには 行 【注文月日】、【氏名】、【商品金額】が記入されています。
Bファイルには 行 【注文月日】、【氏名】、【税金額】が記入されています。

AファイルのD列に、Aファイルの【注文月日】かつ【氏名】がBファイルの【注文月日】かつ【氏名】と一致する場合のBファイルC列の【税金額】を表示したいのです。

VLOOKUP関数では条件が1つだけなので使用できませんが、似たような関数で複数条件ができる関数があるなら教えてください。

Aベストアンサー

こんばんは!
一例です。
↓の画像で右側(Bファイル)の方に作業用の列を設けています。
作業列D2セルを
=A2&B2
としてオートフィルで下へずぃ~~~!っとコピーします。

そして、AファイルのD2セルに
=IF(OR(A2="",COUNTIF([Bファイル.xls]Sheet1!D$2:D$1000,A2&B2)=0),"",INDEX([Bファイル.xls]Sheet1!C$2:C$1000,MATCH(A2&B2,[Bファイル.xls]Sheet1!D$2:D$1000,0)))
という数式を入れ、オートフィルで下へコピーすると
画像のような感じになります。
尚、数式はBファイルの1000行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

以上、参考になれば良いのですが
他に良い方法があれば読み流してくださいね。m(__)m

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

QexcelのVLOOKUPで検索値を2つにできますか?

excelのVLOOKUPで検索値を2つにしたいです
私の知っているVLOOKだと下記のことは対応できます
例えば、A列に会社名、B列に住所、C列に電話番号 とあった場合
「住所が検索値と同一なら電話番号を表示しなさい」という指示は出せます

そこで質問です
「会社名と住所が検索値と同一なら電話番号を表示しなさい」
というような、複数の検索値を持つ事はできないのでしょうか?

参考になるURLなどでも結構ですので、ご存知の方よろしくお願いいたします

Aベストアンサー

元の表をA列に会社名、B列に住所、C列に「=A2&B2」、D列に電話番号のようにして
=VLOOKUP(会社名&住所,$D$2:$C$100,2,FALSE)
のようにすれば可能です。

Qエクセルで重複しているデータの抽出のしかたを教えてください。

エクセルで重複しているデータを抽出したいのですが・・。なにぶん初心者なもので簡単な方法があればよいのですが・・。
A列に日付 B列に企業名このデータが300件ほどあります。

 Å列      B列
1月 1日    AAAA社
1月 4日   BBBB社
1月 8日   CCCC社
2月 1日   BBBB社
2月20日    DDDD社
3月 2日   AAAA社

と、あるとしてB列の企業名が重複しているものを抽出したいのですが・・・。この場合 AAAA社とBBBB社ですが。
これを簡単に抽出する方法はないでしょうか?
教えてください お願いいたします。

Aベストアンサー

企業名がセルB2からセルB300まで入力されていると仮定します。
セルC2に IF(COUNTIF($B$2:B2,B2)>1,"*","") を入力し、セルC300までコピーして下さい。すると重複したデータの二番目以降に "*" のマークが付きます。
(注) 数式を下のセルまでコピーするため、COUNTIF($B$2 は絶対参照にして下さい。


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

このカテゴリの人気Q&Aランキング