教えてください。
図G-K列(下の表)のような標準値を与える表があります。
(実際は150行程度、順番は完全にランダムです)
別表で、図A-C列(上の表)のような「地質」「樹種」「年齢」が、
実測値で手入力されたデータがあります。
(1000行程度)
この上表の各行に既存の3つの条件(「地質」「樹種」「年齢」)に対応した「生長量」「糖度」を、
標準値の表から拾ってきたい場合、どのような関数を指定すればよいでしょうか。
(たとえば、図だとD9には850、E9には4が入るようにしたい)
VLOOKUP関数を試しましたが、複数の参照列がある場合は適当でないようでした。
やはりデータベース関数でしょうか?
浅学な質問で申し訳ありませんが、よろしくご教授ください。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.2
- 回答日時:
こんばんは!
一例です。
↓の画像のように作業用の列を設けています。
作業列L3セルを
=G3&H3&I3
としてオートフィルでずぃ~~~!っと下へコピー!
D3セルに
=IF(A3="","",IF(COUNTIF($L:$L,$A3&$B3&$C3),INDEX(J:J,MATCH($A3&$B3&$C3,$L:$L,0)),""))
という数式を入れ列方向と行方向にオートフィルでコピーすると画像のような感じになります。
参考になれば良いのですが・・・m(_ _)m
No.3
- 回答日時:
作業列を使用しない方法としては次の様なものがあります。
まず、D3セルに次の数式を入力して下さい。
=IF(AND($A3<>"",$B3<>"",$C3<>"",SUMPRODUCT(($G$2:INDEX($G:$G,MATCH("゛",$H:$H,-1))=$A3)*($H$2:INDEX($H:$H,MATCH("゛",$H:$H,-1))=$B3)*($I$2:INDEX($I:$I,MATCH("゛",$H:$H,-1))=$C3))=1),INDEX(J:J,SUMPRODUCT(ROW($H$2:INDEX($H:$H,MATCH("゛",$H:$H,-1)))*($G$2:INDEX($G:$G,MATCH("゛",$H:$H,-1))=$A3)*($H$2:INDEX($H:$H,MATCH("゛",$H:$H,-1))=$B3)*($I$2:INDEX($I:$I,MATCH("゛",$H:$H,-1))=$C3))),"")
次に、D3セルをコピーして、E3セルに貼り付けて下さい。
次に、D3~E3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。
以上です。
尚、I列~K列の値が、必ず数値データのみである場合には、D3セルに入力する数式を次の様なものとする事も出来ます。
=IF(AND($A3<>"",$B3<>"",$C3<>"",SUMPRODUCT(($G$3:INDEX($G:$G,MATCH(9^99,$I:$I))=$A3)*($H$3:INDEX($H:$H,MATCH(9^99,$I:$I))=$B3)*($I$3:INDEX($I:$I,MATCH(9^99,$I:$I))=$C3))=1),SUMPRODUCT((J$3:INDEX(J:J,MATCH(9^99,$I:$I)))*($G$3:INDEX($G:$G,MATCH(9^99,$I:$I))=$A3)*($H$3:INDEX($H:$H,MATCH(9^99,$I:$I))=$B3)*($I$3:INDEX($I:$I,MATCH(9^99,$I:$I))=$C3)),"")
No.4
- 回答日時:
数式入力セルが多くなると動きが重くなるのであまりお勧めできませんが、例示のレイアウトなら以下のような数式をD3セルに入力して方向にオートフィルすれば該当データを表示できます。
(エラー処理はしてありません)=INDEX(J$3:J$1000,MATCH($A3&$B3&$C3,INDEX($G$3:$G$1000&$H$3:$H$1000&$I$3:$I$1000,),0))
#Officeソフトはバージョンによって使用できる機能(Excelの場合は関数など)や操作方法が大きく異なりますので、質問の際には必ずバージョンを明記するようにしましょう。
No.5
- 回答日時:
エクセルの複数条件での抜き出しや参照は関数式が長くなって、式の理解も難しい。
既に回答があるとおり。興味があれば、Googleででも「imogasi方式」で照会すれば、過去の抜き出し問題と回答のタイプが判る。
ーー
そういう事情で、エクセルでそれが出来るのは、データーフィルターフィルタオプションの設定しかない。
他にはデータベースのSQLなど使わないと複雑になる。
ただしフィルタオプションの操作は、1条件に1件分しか出来ない。これをVBAで連続作業処理をするようにしてみた。
ーー
例データ
Sheet1
地質樹種年令成長量
abc
def
jjkl
kkmmaa
成長量の列にSheet2を参照した値を入れるのが目的
Sheet2 検索表
地質樹種年令成長量
abc23
def2
ghi3
jjkl4
kkmmaa45
ーー
Sheet2
G1:I2 条件をセットするセル範囲
地質樹種年令
kkmmaa<-仮の値。空白でセル良い。
ーー
G5:J6 結果をセットするセル範囲
地質樹種年令成長量
kkmmaa45 <--仮の値。空白セルで良い。
結果はVBA実行後、Sheet2のJ6セルにセットされる
ーーー
標準モジュールに
Sub Macro4()
'--シートの定義
Dim sh1, sh2
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
'--シート1の最下行をとらえる
d1 = sh1.Range("A65536").End(xlUp).Row
MsgBox d1
For i = 2 To d1 'シート1の最下行まで各行で繰り返し
'--シート2へ条件の値を、シート1の行からセット
sh2.Range("G2") = sh1.Cells(i, "A")
sh2.Range("H2") = sh1.Cells(i, "B")
sh2.Range("I2") = sh1.Cells(i, "C")
'---フィルタオプションの設定をシート2で実行。マクロの記録から
sh2.Range("A1:D12").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=sh2.Range( _
"G1:I2"), CopyToRange:=sh2.Range("G5:J10"), Unique:=False
'--結果をシート1のD行にセット
sh1.Cells(i, "D") = sh2.Range("j6")
Next i
End Sub
マクロの記録で、出るコードを一部修正した程度のもの。
ーー
結果
Sheet1 のD列に求めるものが出る。
地質樹種年令成長量
abc23
def2
jjkl4
kkmmaa45
成長量のほかに他項目も1度に取りたいときはコードの修正追加・シート2のK列の項目見出しの追加が必要。略。
ーー
本件で3列((「地質」「樹種」「年齢」)を文字列結合して、その列でVLOOKUP関数を使うのが考えやすいかも。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Excel(エクセル) ExcelのVLOOKUP関数 7 2022/08/23 06:46
- その他(プログラミング・Web制作) プログラミング pythonの問題について 2 2022/04/19 00:41
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
- Excel(エクセル) 関数の質問です 3 2022/03/24 12:41
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- Excel(エクセル) Excel処理について、教えて下さい。 下記表は、サンプルです。(実際には千件以上あります) A列に 6 2023/03/16 18:21
- Excel(エクセル) ExcelVBAでリストの項目に必要数と同じ手配数を分配していくマクロを作りたいです。 1 2022/07/29 18:36
- その他(Microsoft Office) Excel2019と365、2021 2 2023/07/08 06:22
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
【関数】先頭だけにある、半角...
-
エクセル 白黒印刷で白線を印刷...
-
Excelのチェックボックスの使い...
-
【関数】適切な文字数の数字を...
-
Excelのpivotについて質問です
-
Excel ピボットテーブルで日付...
-
LOOKUP関数を使えばいいのでし...
-
エクセル関数を教えてください
-
エクセルのセルに同じ大きさの...
-
UNIQUE関数が使えないバージョ...
-
excelの不要な行の削除ができな...
-
エクセルで「-0.0」と表示さ...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの関数について教えて...
-
Aというブックの1というシート...
-
【マクロ】シート名を取得する...
-
VBA Private Sub Worksheet_Cha...
-
VBA、Excelのworkbook.open に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報