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

お世話に成ります。
エクセル2010のVLOOKUP関数で、例えばA列に日付が入っていて、B列に人の名前が入っていて、日付けで検索した場合にA列に同じ日付が有って、B列に違う名前が有った場合の処理について教えてください。

やりたいのは、上から順に名前が引き当てられる。 <表示例 1>
これは当方には難しい気がするので、第2希望としては全ての該当氏名が同じ日付に引き当てられる。
<表示例 2>
この場合、表示させるセルの場所の指定が煩雑になりそうな気がしますが、何とかできないでしょうか?

A列       B列
2017/7/1    Aさん
2017/8/1    Bさん
1017/7/1    Cさん

<表示例 1>
検索値    結果 
2017/7/1   Aさん
2017/7/1   Cさん

もしくは
<表示例 2>
検索値    結果 
2017/7/1   Aさん、Cさん

A 回答 (5件)

VLOOKUP関数ではなくて、フィルターの詳細設定で表示例1のようにできます。


[データ]→フィルター横にある[詳細設定]で。
「エクセルのVLOOKUPで1つの検索値に」の回答画像2
    • good
    • 0
この回答へのお礼

なるほど!
フィルターオプションとフィルターの組合せでズバリの結果が得られました。
ありがとうございました!
関数以外の発想が無かったので非常に参考になりました。

お礼日時:2017/07/19 11:20

こんにちは!



とりあえず「表示例1」の場合です。
元データはSheet1にあり
表示例1の場合はSheet2に表示するとします。

↓の画像のように作業用の列を設けてみてはどうでしょうか?
作業列D2セルに
=IF(AND(COUNTIF(A:A,A2)>1,COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1),ROW(),"")
という数式を入れこれ以上データはない!というくらいまでフィルハンドルで下へコピーしておきます。

Sheet2のA2セルに
=IFERROR(INDEX(Sheet1!A:A,SMALL(Sheet1!$D:$D,ROW(A1))),"")
という数式を入れ、列・行方向にフィル&コピー!
A列の表示形式を「日付」にすると
画像のような感じになります。

※ 表示例2の場合、「結果」は一つのセルに表示させたいのでしょうか?
その場合はやはりVBAで処理する方法になると思います。m(_ _)m
「エクセルのVLOOKUPで1つの検索値に」の回答画像5
    • good
    • 0
この回答へのお礼

マクロは苦手なので何とか関数で、と思い質問させていただきました。
ネットで調べたり、ここで教えて頂いたり、いろんな関数の組合わせで出来るのですね。
色々試してみて一番自分にあった(分かり易い)関数の組合せを勉強させていただきます。

因みに結果は1つのセルにこだわりはないのですが、マクロは避けたい。
今回は質問(関数)と違ってはいますがNo2さんの方法で一発解決ですのでNo2さんをBSにさせて頂きました。
当方がBS選択でまごついている間のご回答でしたのでご了承ください。

お礼日時:2017/07/19 13:03

E2; =IFERROR(INDEX($B:$B,SMALL(IF($A:$A=$D2,ROW(A:A),""),E$1)),"")


【お断り】上式は必ず配列数式として入力のこと
「エクセルのVLOOKUPで1つの検索値に」の回答画像4
    • good
    • 0
この回答へのお礼

有難うございます。
配列数式は2枚のシートで使用しているのですが、いづれもすったもんだの結果ですので、今一どころか全く自信なし。
いづれは必要になると思うので、これで練習させていただきます。

BS決定にまごついている間のご回答で、お手数をおかけしました。

お礼日時:2017/07/19 13:10

んーと。

それはVLOOKUP関数ではできません。

…ではどうするか。
他の関数を組み合わせて処理を行います。

例えばAA列を作業用の列として、
AA1セルに
 =IF(A1="2017/7/1",1,0) 
(…この数式は、ちょっと嘘を含みます。"2017/7/1"の部分は検索値の日付を入力しているセル番地を入力してください)
と数式を記入し、必要な行まで複製します。
すると、"2017/7/1"の日付の行には「1」、違う場合は「0」が表示されるようになります。

次、
AB列をさらに作業用の列として
AB1セルに
 =IF(AA1=1,SUM(OFFSET(AA$1,0,0,ROW(),1)),0)
と数式を記入し、必要な行まで複製します。
すると、AA列に「1」と表示されている行でカウントが始まります。

これであとはMATCH関数でAB列の数字を1から順に上から何番目にあるかをしらべ、
対応するB列の行の値をINDEX関数で参照すれば、<表示例1>のように作ることも<表示例2>のように作ることもできます。
(質問の例では、AB列に「1」が表示されるのは”Aさん”の行で、「2」が表示されるのは”Cさん”の行です)
最終的に
 =IFERROR(INDEX(B:B,MATCH(ROW(A1),AB:AB,0),"")
これを必要な行まで複製すれば<表示例1>
 =IFERROR(INDEX(B:B,MATCH(COLUMN(A1),AB:AB,0),"")
これを必要な列まで複製すれば<表示例2>
のような形にできます。

※ IF、OFFSET、SUM、MATCH、INDEX、ROW、COLUMN、IFERROR
 の各関数の使い方を確認して理解したうえで使うようにしましょう。


・・・余談・・・
ちょっと難しい使い方の「配列数式」にすることで、作業列を作らずに一つのセル(行、列)でできるのですが、
考え方が分からなければ意味がありませんので、このように処理手順を分けて説明をしました。
※そのままコピーして「うまく動かない」と言う人が稀にいるんです。

難しい使い方をしなくても、基本の関数を使う事ができれば、このように処理ができるという例です。
…ほんとはもっと数式を分解して説明を入れたかった…
    • good
    • 0
この回答へのお礼

早々のご回答ありがとうございました。
配列数式やマクロも使ってはいますが、全く自信がなく、何とか汎用の関数の組合せで出来ないかと思い質問させていただきましたが、こんなに多くの関数を組み合わせられたことにびっくりしています。(やはり聞いてみるものだと。。。)

COLUMN 以外の関数は割とよく使ていますので、教えて頂いた式の意味は十分に理解できます。
但し、当方の場合、組合せたら先ず1回でうまく動くことはなく、試行錯誤の繰り返しで何とかかんとかのレベルです。
教えてもらうと非常に簡単に組合せて解決するのが情けない(とほほ・・・)

教えて頂いた式はコピーを取って、「エクセルテク」のフォルダーに入れていつか使用させてもらいますが、今回のケースに限ってはNo2さんの方法で非常に簡単に解決できましたのでNo2さんをBSにさせて頂きますのであしからず。

こんなにたくさんの関数を組み合わせたことは無いので貴重な例として参考になりました。
今後この式(または1部分)は必ず使用することになりますので感謝!!!!

お礼日時:2017/07/19 11:57

基本的に複数を引き当てるという考え方は関数にはありません。


なぜならばその関数を記述するセルが単一だからです。
当然VLOOKUPにおいても最初に引き当てたもの以外は無視されます。

では 表示例1を考えてみましょう。
仮にCさんの方を出す方法があるとしても
2行目の検索値はどうするのですか?
COUNTIFを使って予め何個あるかを数えることは可能ですが
それを別のセルに適用することはマクロ以外ではかなり煩雑になると思われます。

そういう意味においては表示例2の方がまだ少しは楽でしょうね。
但し、2個なら考えられる可能性はなくもないけど3個以上もとなると
やはりマクロの方が合理的ではないかと。
    • good
    • 0
この回答へのお礼

早々のご回答ありがとうございます。
やはりマクロでしょうか。
頑張って勉強します!

お礼日時:2017/07/19 11:18

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

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

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

Qエクセルの関数で

以下の算式を満たすエクセルの関数を教えていただけないでしょうか。

セルA1(①) セルA2(②) セルA3(③) セルA4(④) セルA5(⑤)


③=①-②
④=③÷② 小数点3位未満切り捨て
⑤ ④が0.05超の場合  0.09+((④-0.05)×0.3  (上限0.1) 小数点3位未満切り捨て
④が0.05以下の場合 0.09-((0.05-④)×0.1  (下限0.06) 小数点3位未満切り捨て


ここの⑤を満たす式を教えてください。

例えば①531,116,991②646,729,949のとき⑤は0.067となります。

よろしくお願いいたします。

Aベストアンサー

0.05超の場合の式…⑥
0.05以下の場合の式…⑦
IF(④>0.05,IF(⑥>0.1,ROUNDDOWN(⑥,×),0.1),IF(⑦<0.06,0.06,ROUNDDOWN(⑦,×)))

×部分はうろ覚えです、小数点第三位なら「3」…ですかね…?

以上でどうでしょう

Qexcelで条件に合うよう、複数のセルの合計を求めたい

例えば、次のように並んでいるセルの数値があるとします。

1515
2748
540
5509
2195
680
7142
305
5042
530
667
325
9950
4800

その合計が30000以上で、かつ、最小の数字となるよう、複数のセルを選択したいと思いますが、これを実現できる関数はありますか?

Aベストアンサー

いわゆる「ナップザック問題」と呼ばれる種類の問題になると思います。

14個を選択する/しないなら、総当りしても16384個ですから、力技でも行けるかも。
画像を参考に、
1行目にデータの数値を横並び。
2行目に0~13の固定値を右から
3行目に2の0乗~2の13乗(8192)の固定値を右から
A列に0~16383の固定値
で、

B4:O16387の範囲に、
B4:=MOD(INT($A4/B$3),2)
をコピペして2進数の各桁の値を

P4:P16387の範囲に、
P4:=SUMPRODUCT($B$1:$O$1,B4:O4)
をコピペして、2進数の各桁のビットとデータの数値の積和

で、全16384通りの計算が行われるので、A4:末尾を選択して並べ替えすると、
合計が30002となる、
1515
2748
2195
680
7142
305
667
9950
4800
が確認できるとか。

--
もっと数値の数が増えると、この方法では厳しいので、

ナップザック問題をExcelで解く
http://www.geocities.co.jp/SiliconValley-Oakland/8139/

みたいなプログラムで解くような事になります。


条件が違うのでプログラムはそのまま使えませんが、似た質問。

エクセルで、「袋詰め問題」を解きたい - Excel(エクセル) 解決済 | 教えて!goo
https://oshiete.goo.ne.jp/qa/1255891.html

いわゆる「ナップザック問題」と呼ばれる種類の問題になると思います。

14個を選択する/しないなら、総当りしても16384個ですから、力技でも行けるかも。
画像を参考に、
1行目にデータの数値を横並び。
2行目に0~13の固定値を右から
3行目に2の0乗~2の13乗(8192)の固定値を右から
A列に0~16383の固定値
で、

B4:O16387の範囲に、
B4:=MOD(INT($A4/B$3),2)
をコピペして2進数の各桁の値を

P4:P16387の範囲に、
P4:=SUMPRODUCT($B$1:$O$1,B4:O4)
をコピペして、2進数の各桁のビットとデータの数値の積...続きを読む

Qエクセルの足し算について質問です。

エクセル2003を使っています。
A1~A10までの数字の合計を、B10に一発で表示させるのはどうしたらいいでしょうか?
オートSUMでは、合計はA11にしか表示されないのでしょうか?

Aベストアンサー

こんにちは!

Excelのバージョンに関係なくやり方は一緒だと思います。

B10セルを選択 → オートサムのアイコン(Σ)をクリック → A1~A10セルを範囲指定(ドラッグ)

これで大丈夫では?m(_ _)m

Qエクセルの重複データを見つけて統合したいです。

みなさんお忙しいところすみません、教えてください。

AとA‘という2つの表があります。データは行で1700件ほどです。
どちらの表にも
エクセルのA列は識別番号で共通の数字が並んでいます。

Aの表には、ABCDEの列で作られています。
A‘の表にはABCDEは同じ内容のセルで、FGH列は別な内容のセルがあります。

A列の識別番号の1700件のなかで、ABCDEを持つ人に、FGHを持つ人を
合成させるためには

どのようにしたらいいでしょうか。

Aベストアンサー

シート名に'は使え無いので
A表をSheet1、A'表をSheet2とすると

Sheet1のF2、G2、H2に以下の関数を入れて、下までコピペする
F2:=VLOOKUP(A2,Sheet2!$A$2:$H$1701,6,FALSE)
G2:=VLOOKUP(A2,Sheet2!$A$2:$H$1701,7,FALSE)
H2:=VLOOKUP(A2,Sheet2!$A$2:$H$1701,8,FALSE)

Qエクセルで三つの条件での参照する方法。 お世話様です。 コード 項目 7月 8月 9月 10月 11

エクセルで三つの条件での参照する方法。
お世話様です。

コード 項目 7月 8月 9月 10月 11月 12月
1234 野菜 8 11
9876 肉類 5 4
小計 5 8 11 4
2345 魚類 12 8
3456 麺類 1 6
小計 1 6
合計

このようなデータがあります。
コードは1000くらいあります。
小計も100くらいあります。

別シートにこれよりも項目が多い表があります。
この表に上記データを反映させたいです。

作業列を作りコードと項目を&で合体させ
ます。
それを検索値とし、別シートの7月の列にvlookup関数にします。
コードの列をフィルタ掛けし小計、合計を非表示にします。
貼り付け。

これを月別に貼り付けしようと思います。
他に効率良い方法ありますか?
言葉足らずな点は補足します。
よろしくお願いいたします。

エクセルで三つの条件での参照する方法。
お世話様です。

コード 項目 7月 8月 9月 10月 11月 12月
1234 野菜 8 11
9876 肉類 5 4
小計 5 8 11 4
2345 魚類 12 8
3456 麺類 1 6
小計 1 6
合計

このようなデータがあります。
コードは1000くらいあります。
小計も100くらいあります。

別シートにこれよりも項目が多い表があります。
この表...続きを読む

Aベストアンサー

素朴な確認をば。
小計行の数値 5、8、11、4 はどのセルとどのセルとの小計になっているの?
「あれは、単に書いたまでで、実はデタラメ!」なんて言わないでネ。回答者は真剣なので。

Qエクセルで休憩時間を引く時と、引かない時の数式

エクセルで、
「出~退」が4時間以内なら、その間の時間が表示され、
「出~退」が4時間を超えるなら、その間の時間から休憩1時間を引いた時間が表示されるようにしたいです。

「稼働時間」のセルは、どのような数式にすれば良いですか。

氏名 日付 出 退 稼働時間
三田 建造 2017/5/30 8:22 17:33 8:11
藤島 澄人 2017/5/30 9:00 12:57 3:57

Aベストアンサー

E2: =D2-C2-"1:0"*(D2-C2>"4:0"*1)

QExcelの関数について、教えてください!

すみません。こんなことができるか分からないのですが、Excelでやりたいことがあります。
本当は全自動で計算できればいいのですが、多少、人間の手が介入しても構いません。プログラミングなどは分からないので、なるべくExcelでやりたいです。関数もそこまで詳しくないので、できたら分かりやすく教えていただけると嬉しいです。どうぞよろしくお願いします。

やりたいこと。
例えば、Aの行に、日時があり、Bの欄にデータがあります。下記参照ください。

A B
1月1日 100
1月2日 99
1月3日 101
1月4日 104
1月5日 94
1月6日 110
1月7日 112
1月8日 115
1月9日 109
1月10日 115
1月11日 120
1月12日 125

やりたいことが2段階になっています。

1、ある値を決めてその値が出た以降のデータを使いたいです(それ以前のデータは使わない)。例えば110という値を指定した場合、1月6日に110という値が出るので、1月6日以降だけのデータを使いたい。

2、その後、6日以降のデータで、データのバラツキを見たいので、例えば110の±10の範囲でその範囲を超えたときに、この表では、1月11日に120を超えるので、超えたポイントの日付を出して欲しいです。


例えばC1に110(110からチェックを開始)をD1にバラツキ範囲(±10)を入力したら、下記のような結果を出すことは難しいでしょうか??

A B C D
1月1日 100 110 10
1月2日 99
1月3日 101
1月4日 104
1月5日 94
1月6日 110
1月7日 112
1月8日 115
1月9日 109
1月10日 115
1月11日 120
1月12日 125

結果
チェックスタートした日付 1月6日
範囲超えた日付と方向 1月11日 (+)

かなり難しく、私では手作業での確認となっております。どうぞいい案がありましたら、教えてください。

すみません。こんなことができるか分からないのですが、Excelでやりたいことがあります。
本当は全自動で計算できればいいのですが、多少、人間の手が介入しても構いません。プログラミングなどは分からないので、なるべくExcelでやりたいです。関数もそこまで詳しくないので、できたら分かりやすく教えていただけると嬉しいです。どうぞよろしくお願いします。

やりたいこと。
例えば、Aの行に、日時があり、Bの欄にデータがあります。下記参照ください。

A B
1月1日 100
1...続きを読む

Aベストアンサー

こんにちは!

一例です。
↓の画像でG1セルに
=MIN(IF(B1:B1000>=C1,A1:A1000))
配列数式なのでCtrl+Shift+Enterで確定!

G2セルに
=MIN(IF((ABS(VLOOKUP(G1,A1:B1000,2,0)-B1:B1000)>=D1)*(A1:A1000>G1),A1:A1000))
これも配列数式なので、Ctrl+Shift+Enterで確定!

G3セルに
=IF(SIGN(VLOOKUP(G1,A:B,2,0)-VLOOKUP(G2,A:B,2,0))>0,"(-)","(+)")

G3セルだけは配列数式ではありません。
G1・G2セルの表示形式を「日付」にして完了です。

※ エラー処理をしていませんので、
データが存在しない場合は「1月0日」と表示されたり
エラーになるかもしれません。m(_ _)m

Qエクセルで特定の数字だけ勝手に変換されてしまいます

エクセル2010で48,609.793と入力するのですが
入力後のセルを見てみると48,609.792999999と
勝手に変換されてしまいます。
48,609.792や48,609.794ではそのままなのですが
なぜなのでしょうか?
計算上で入力した数値を使用したく困っています。

Aベストアンサー

10進数をコンピュータ内部では2進数の形で保持することによる誤差だそうです。
なるべくこの誤差が出ないようにExcelは工夫されているそうですが、
特定の数字を入力すると「99999…」が現れるのだとか。
この質問を見て調べるまで全然知りませんでした。
https://answers.microsoft.com/ja-jp/msoffice/forum/msoffice_excel-mso_other/excel/bbaaa7f0-5e73-4d7c-aa05-6709a3357d26
https://support.microsoft.com/ja-jp/help/813530

計算式には数字を丸める関数を入れると対処できると思います。
ROUNDやROUNDUP関数で引数を3にしてみてください。
http://kokoro.kir.jp/excel/round.html

Qエクセル 論理式

エクセルで

A1*B1が100未満の時は100と表示、そうでないときはそのまま計算
という場合
=IF(SUM(A1*B1)<100,100,SUM(A1*B1))
になると思うんですけど、

この場合、
A1B1に入力データが無い時(計算してほしくないとき)でセル値が空白の場合も100と計算されてしまいますよね。

例えばそれを、
A1*B1が100未満の時は100と表示、そうでないときはそのまま計算、しかしセル値が空白で計算結果が0(100未満該当)のときは計算しないもしくは0と表示。
みたいにする方法はありますか?

Aベストアンサー

=(A1*B1>0)*MAX(A1*B1,100)
または
=IF(A1*B1,MAX(A1*B1,100),0)

QMSの“小さな親切、余計なお世話”

Excel 2013 を使用しています。
添付図上段において、セル F1 に(赤矢印の先に)何やら表示されているアイコン、セル内に入力された文字列を隠しています。其処にマウスポインタを乗せると[挿入オプション]と表示され、当該アイコンの右端に現れたナビスコマークをクリックすると、添付図下段に示すメニューが。

この邪魔臭いアイコンを隠す(永久に表示されなくする)ための設定手順を教えてください。
ちなみに、このアイコンはどういう場合に表示されるようになっているのでしょうか?

Aベストアンサー

2016のオプション画像で申し訳ない。

「コンテンツを貼り付けるときに[貼り付けオプション]ボタンを表示する」
このチェックを外す。


人気Q&Aランキング