こんばんは。
いつもお世話になります。
以下の関数に関してよい方法が見当たりませんでしたのでとぴにより質問をさせて頂きます。
{シート1}
A B C D
生徒ID 算数 国語 社会
A001
A002
A003
...
{シート2}
A B C
生徒ID 教科 評価
A001 算数 C
A001 国語 B
A001 社会 A
A002 算数 B
A002 国語 A
A002 社会 C
...
やりたいこと。
{シート1}の生徒A001の算数のセルに{シート2}の2つの条件(A001,算数)にあう評価(A,B,C)を返したい。
以前、以下のような数式を教えて頂きました。
このときは評価(文字)ではなく数値を返すものなので達成できましたが文字にしてからエラーが出てしまいます。
-------
◆Sheet1のB2の式
B2=SUMPRODUCT((Sheet2!$A$2:$A$10=$A2)*(Sheet2!$B$2:$B$10=B$1)*Sheet2!$C$2:$C$10)
-------
ご指導頂きたく、どうぞ宜しくお願い致します。
No.3ベストアンサー
- 回答日時:
例データ Sheet1 A1:C10
生徒ID教科評価
A001算数C
A001国語B
A001社会A
A002算数B
A002国語A
A002社会C
A003算数B
A004国語D
A005社会D
Sheet1に(下記A、B、C、Dは式の結果答えも出た様子)
生徒ID算数国語社会作業列
A001CBA0
A002BAC3
A003BDD6
式を簡単にするため、作業列はどこか秋列に作る(ここではF列とする)
式は=MATCH(A2,Sheet2!$A$2:$A$100,0)-1
意味は生徒IDの初出(開始)行のB1からの隔たり行数を出している。
ーー
B2に
=OFFSET(Sheet2!$A$2,$F2+MATCH(B$1,OFFSET(Sheet2!$B$2,$F2,0,100,1),0)-1,2)
B2no式をB列下方向に生徒数分、b2:B4の式を右方向に科目数分
式を複写する(+ハンドルを引っ張る等)
結果
上記の通り。
作業列はF列の式をB2の式にネストして、、組み込めば不要となるのは
ご存知でしょうが、理解のため、そのままにします。
ーー
やっているのはMATCH関数で生徒IDや科目命をみつけている。
ただし(B2以下でなく)その生徒IDの開始行以下で科目名を探さないといけないので、その分修正しているので複雑化している。
ご回答が遅くなりました。
ご提示頂きました計算式で試してみましたら、目的の形になりました。
すばやい対応を頂きありがとうございました。
また、どうぞ宜しくお願い致します。
No.2
- 回答日時:
シート2 のデータが規則性を持って並んでいるのであればセルの位置を計算で指定できますから
INDIRECT関数で 示すことができると思います。
シート1のB2セルにはA001の数学の評価が入るのであれば、
=INDIRECT("シート2!C2")
で示すことができます。
括弧の中は他の関数に置き換えることで規則性を持たせることができます。
教科の数が3つなら
B2 =INDIRECT(ADDRESS((ROW(B2)-2)*3+(ROW(B2),3,,,"シート2"))
C2 =INDIRECT(ADDRESS((ROW(C2)-2)*3+(ROW(C2),3,,,"シート2"))
D2 =INDIRECT(ADDRESS((ROW(D2)-2)*3+(ROW(D2),3,,,"シート2"))
教科の数が5つなら
B2 =INDIRECT(ADDRESS((ROW(B2)-2)*5+(ROW(B2),3,,,"シート2"))
C2 =INDIRECT(ADDRESS((ROW(C2)-2)*5+(ROW(C2),3,,,"シート2"))
D2 =INDIRECT(ADDRESS((ROW(D2)-2)*5+(ROW(D2),3,,,"シート2"))
E2 =INDIRECT(ADDRESS((ROW(E2)-2)*5+(ROW(E2),3,,,"シート2"))
F2 =INDIRECT(ADDRESS((ROW(F2)-2)*5+(ROW(F2),3,,,"シート2"))
など(ADDRESS、ROW以外の関数を使用しても可能です)
A002以下の行は、A001の数式をフィルでコピーするだけです。
規則性がない状態で入力されているとは考えにくいので、この方法で十分な気がするのですが如何でしょう。
※使用している関数の詳細はExcelのヘルプを参照してください。
No.1
- 回答日時:
シート1のB2に以下の式を入れます
=OFFSET(シート2!$C$1,SUMPRODUCT((シート2!$A$2:$A$700=$A2)*(シート2!$B$2:$B$700=B$1)*ROW(シート2!$A$2:$A$700))-1,0)
このセルを右方向、下方向にコピー
シート2に該当するデータが無い場合は、#REF!エラーになりますが、エラーになる方が点検はしやすいと思います。
なおシート2のデータは700行まであるものとして式を書いていますので、それ以上なら変更して下さい
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセル関数について 2 2022/04/13 18:25
- Excel(エクセル) Excelにて、行の最後のセルの値をコピーして別sheetに張りつけるVBAコードをご教授願います 3 2022/11/20 14:35
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- その他(Microsoft Office) Excel 2列の値を返す数式についてです 1 2022/11/23 22:59
- その他(Microsoft Office) ある表(10桝程度)の中に数字が入っています。ダブっている数字を除く数字の合計数の計算方法 5 2023/02/15 11:33
- Excel(エクセル) ある数値に対して、値を返す数式についてです 2 2022/09/13 22:06
- Excel(エクセル) エクセルで”入力シート”の文字書式の変更を”出力シート”で同じ文字書式で印刷したいです。VBA希望 4 2023/04/24 11:07
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) 至急です><Excelの関数を教えてください。 2 2022/03/22 17:56
- Visual Basic(VBA) VBA 最終行まで数式をコピーする 3 2023/01/03 15:44
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで「-0.0」と表示さ...
-
【マクロ】シート名を取得する...
-
エクセルの条件付き書式につい...
-
【マクロ】毎回、ファイル名が...
-
エクセルで 例えば 伊藤と名前...
-
excel で二つのどちらかを選ぶ
-
Aというブックの1というシート...
-
Excel 2019 のピボットテーブル...
-
マクロの有効化するダイヤログ...
-
Excel元に戻す方法を教えてくだ...
-
写真のコピー
-
VBA Private Sub Worksheet_Cha...
-
【マクロ】フォルダからエクセ...
-
Excelでの時間帯の入力
-
文字列になっている時間をVBAで...
-
エクセルの順位別一覧表の自動...
-
Excelはなんで先頭の0を消すん...
-
行数が不規則な一週間ごとの合...
-
ある列、或いは、ある行のセル...
-
エクセルで特定の範囲内から小...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報