Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
複数の値を列から値を返す
投稿日時: 20/02/02 17:32:11
投稿者: 桃太郎7

宜しくお願いします。
先回の質問の仕方に問題がありまして申し訳ありませんでした。
 
改めて、質問をさせて頂きます。
列横から複数の値を返す事で悩んでいます。
 
 質問例
    A B・・D E・・I J・・M N・・・・・・・EH EI  EL列に返す  データはA:EIまで
 1  E S1・・A S2・・B S1 E S1・・・・・・B S1   E S1 3
 2  B S2・・E S1・・A S2 B S3・・・・・・E S3   B S1 5 
 3  A S2・・A S1・・B S2 A S2・・・・・・A S3   A S1 7   
 4  E S2・・B S1・・E S3 A S1・・・・・・B S3   E S2 4   
 5  B S2・・A S1・・A S1 E S3・・・・・・E S3   B S2 5 
 6  E S2・・A S1・・B S3 B S3・・・・・・A S3   A S2 5 
 7  A S2・・B S1・・E S2 A S3・・・・・・B S3   E S3 7    
 8  E S2・・A S1・・B S1 E S3・・・・・・E S3   B S3 5 
 9  B S2・・A S1・・B S2 E S3・・・・・・A S3   A S3 4 
                  合計 9*5  45    合計 45
 
    質問例題が難しくてこれでご理解いただけますでしょうか?
 
    宜しくお願いします
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

回答
投稿日時: 20/02/02 18:57:11
投稿者: んなっと

  A  B C D E  F G H I  J K L M  N O P Q  R   EJ EK EL
1 E S1    A S2    B S1    E S1    B S1    E S1 3
2 B S2    E S1    A S2    B S3    E S3    B S1 5
3 A S2    A S1    B S2    A S2    A S3    A S1 7
4 E S2    B S1    E S3    A S1    B S3    E S2 4
5 B S2    A S1    A S1    E S3    E S3    B S2 5
6 E S2    A S1    B S3    B S3    A S3    A S2 5
7 A S2    B S1    E S2    A S3    B S3    E S3 7
8 E S2    A S1    B S1    E S3    E S3    B S3 5
9 B S2    A S1    B S2    E S3    A S3    A S3 4
 
EL1
=SUMPRODUCT((MOD(COLUMN(A$1:EH$500),4)=1)*(A$1:EH$500=EJ1)*(B$1:EI$500=EK1))
下方向・↓

投稿日時: 20/02/02 21:54:17
投稿者: 桃太郎7

いろいろとお世話をお掛けしています。
ご回答頂きました数式を入れてみましたがエラー表示がでますので、実際のレイアウト計算部分を
記述致します。
 
  N O  U V  AB AC  AI AJ・・・EI    ER ここに個数を返す
7  E S1  A S1  B  S2  A  S2     E S1 3・・エラー表示 
8  B S2  E S1  A  S2  B  S3     E S2 2
9  A S2  E S1  B  S2  A  S2     E S3 2
10 B S2  A S1  B  S2  E  S3     B S1 1
11 E S2  B S1  B  S2  A  S3     B S2 6
12 A S2  A S1  E  S2  E  S3     B S3 1
          合計 24         A S1 3
                       A S2 5
                       A S3 1
〜                      合計 24

90行
(検索範囲N7:EI12)ブロック (6)*14
宜しくご指導ください。

回答
投稿日時: 20/02/03 09:31:30
投稿者: んなっと

式を範囲にあわせるのと、=1の部分を変えるだけですが、どうしても気になってしまうので質問させてください。
範囲を正しく申告しなかったのが原因なのに、まるで式に問題があるかのような下の発言。

引用:
ご回答頂きました数式を入れてみましたがエラー表示がでますので、実際のレイアウト計算部分を
記述致します。

なぜこんなことが書けるのですか?納得のいく理由を書いてください。
 
 
【参考:質問内容の変遷】
ひとつ前のスレッド
https://www.moug.net/faq/viewtopic.php?t=79074
投稿日時: 20/01/31 15:40:58
引用:
                求める答え
  A列 B列 C列 D列      E列へ答え  個数 
1 E  S1  A  S2      E S1 A S2  2
2 F  S1  E  S1      F S1 E S1  1
3 G  S2  F  S1      G S2 F S1  2  
4 E  S1  F  S2      E S1 F S2  1
5 G  S2  F  S1      F S2 G S2  2
6 F  S2  G  S2      G S1 A S2  1
7 F  S2  G  S2          合計  9
8 E  S1  A  S2
9 G  S1  A  S2 
 
 
投稿日時: 20/01/31 18:34:19
引用:
   A  B  C  D   E
 1 A列 B列 C列 D列 個数
 2  E  S1  A  S2   2
 3  F  S1  E  S1   1
 4  G  S2  F  S1   2
 5  E  S1  F  S2   1
 6  G  S2  F  S1   
 7  F  S2  G  S2   2
 8  F  S2  G  S2   
 9  E  S1  A  S2   
10  G  S1  A  S2   1
  
ご回答頂いた上記の解答例の数式を教えて下さい。

 
投稿日時: 20/02/01 12:33:27
引用:
質問の内容が間違っていました・・・ごめんなさい!
横検索の数式をお願いします。        求めたい答え
 1 A列 B C D E・・BQ列まで  A列:B列 BR列へ返すF.G.H.I.J K列:L列  
 2 E  S1             E S1   3 A S1=2
 3 F  S1             F S1   1  B S1=2           
 4 G  S2             G S2   2 C S3=2
 5 E  S1              A S2=1
 6 G  S2 C S3
 7 F  S2             F S2   2 B S1
 8 F  S2        A S3=1
 9 E  S1 C S1=1
 10 G  S1             G S1   1 A S1
  合計 9              合計   9 合計 9

 
そして今回のスレッド
投稿日時: 20/02/02 17:32:11
引用:
    A B・・D E・・I J・・M N・・・・・・・EH EI  EL列に返す  データはA:EIまで
 1  E S1・・A S2・・B S1 E S1・・・・・・B S1   E S1 3
 2  B S2・・E S1・・A S2 B S3・・・・・・E S3   B S1 5 
 3  A S2・・A S1・・B S2 A S2・・・・・・A S3   A S1 7   
 4  E S2・・B S1・・E S3 A S1・・・・・・B S3   E S2 4   
 5  B S2・・A S1・・A S1 E S3・・・・・・E S3   B S2 5 
 6  E S2・・A S1・・B S3 B S3・・・・・・A S3   A S2 5 
 7  A S2・・B S1・・E S2 A S3・・・・・・B S3   E S3 7    
 8  E S2・・A S1・・B S1 E S3・・・・・・E S3   B S3 5 
 9  B S2・・A S1・・B S2 E S3・・・・・・A S3   A S3 4 
                  合計 9*5  45    合計 45

投稿日時: 20/02/02 21:54:1
引用:
ご回答頂きました数式を入れてみましたがエラー表示がでますので、実際のレイアウト計算部分を
記述致します。
  
  N O  U V  AB AC  AI AJ・・・EI    ER ここに個数を返す
7  E S1  A S1  B  S2  A  S2     E S1 3・・エラー表示 
8  B S2  E S1  A  S2  B  S3     E S2 2
9  A S2  E S1  B  S2  A  S2     E S3 2
10 B S2  A S1  B  S2  E  S3     B S1 1
11 E S2  B S1  B  S2  A  S3     B S2 6
12 A S2  A S1  E  S2  E  S3     B S3 1
          合計 24         A S1 3
                       A S2 5
                       A S3 1
〜                      合計 24

90行
(検索範囲N7:EI12)ブロック (6)*14

回答
投稿日時: 20/02/03 09:46:58
投稿者: Mike

お示しの表を Sheet1 とし、作業シートとして Sheet2 を用意します。
Sheet2 において、
1.式 =Sheet1!A1&"_"&Sheet1!B1 を入力したセル A1 を下方に
 ̄ ̄ズズーッと(9行目まで)オートフィル ⇒ 範囲 A1:A9 選択
 ̄ ̄⇒ Ctrl+C ⇒ Ctrlキーを抑えたまンまで、セル D1、I1、
 ̄ ̄M1、EH1 をツン、ツン、… ⇒ 最後のセル EH1 をツンした
 ̄ ̄直後に Ctrl+V をパシーッ
Sheet1 において、
2.次式を入力したセル EL1 のフィルハンドルを「エイヤッ!」
 ̄ ̄とマウスの左ダブルクリック
 ̄ ̄=COUNTIF(Sheet2!A$1:EI$9,Sheet1!EJ1&"_"&Sheet1!EK1)
 
結果は下記だけど、2、3行目の数値が異なるのは私が Sheet1 中のデータを1か所だけ転記ミスした所為かも(下図でチェックしてみてください)。貴方はアナタで上記の操作を実行してみてください。
 
Sheet1
 A B C D E … I J … M N … EH EI EJ EK EL
1 E S1  A S2  A S1  E S1  B S1 E S1 3
2 B S2  E S1  A S2  B S3  E S3 B S1 4
3 A S2  A S1  B S2  A S2  A S3 A S1 8
4 E S2  B S1  E S3  A S1  B S3 E S2 4
5 B S2  A S1  A S1  E S3  E S3 B S2 5
6 E S2  A S1  B S3  B S3  A S3 A S2 5
7 A S2  B S1  E S2  A S3  B S3 E S3 7
8 E S2  A S1  B S1  E S3  E S3 B S3 5
9 B S2  A S1  B S2  E S3  A S3 A S3 4

回答
投稿日時: 20/02/03 10:04:22
投稿者: んなっと

  EP EQ ER
 7 E S1 3
 8 B S1 5
 9 A S1 7
10 E S2 4
11 B S2 5
12 A S2 5
13 E S3 7
14 B S3 5
15 A S3 4
 
ER7
=SUMPRODUCT((MOD(COLUMN(N$7:EH$200),7)=0)*(N$7:EH$200=EP7)*(O$7:EI$200=EQ7))
下方向・↓
 
もしかしたらこれでもいけるかもしれません。途中飛んでいる列のデータ次第で。
=SUMPRODUCT((N$7:EH$200=EP7)*(O$7:EI$200=EQ7))

回答
投稿日時: 20/02/03 10:08:45
投稿者: んなっと

この式を見てもらえばわかるかと思いますが、
=SUMPRODUCT((N$7:EH$200=EP7)*(O$7:EI$200=EQ7))
 
N$7:EH$200O$7:EI$200は、
サイズは同じでひとつ右にずれているんです。
サイズが異なると#N/Aエラーになります。
範囲にあわせて修正する場合、注意が必要です。

回答
投稿日時: 20/02/03 10:28:59
投稿者: んなっと

最後の式は
=COUNTIFS(N$7:EH$200,EP7,O$7:EI$200,EQ7)
でもいけます。この場合もサイズに注意。
ただし、途中飛んでいる列にもAやS1などのデータがあるときは使えません。
その場合は
=SUMPRODUCT((MOD(COLUMN(N$7:EH$200),7)=0)*(N$7:EH$200=EP7)*(O$7:EI$200=EQ7))
の方で。

投稿日時: 20/02/03 14:36:40
投稿者: 桃太郎7

うなっと様 いつも大変ご親切にご指導を頂きながらの この度の大変失礼な言動に対しまして
深く反省をすると共に不快な思いをさせてしまいました事をお詫び申し上げます。
これからは、ご指導を頂きます方の事を考えながら説明内容に気を使って質問をします。
 
私なりに、質問内容を整理してみましたので宜しくお願いします。
    N O   U V   AB AC  AI AK・・EH EI ER列に返す
 7 B S1  A S1  A  S2  B  S1・・E  S3 
 8 E S1  A S2  B  S2  E  S3・・
 9 A S2  A S3  B  S1  A  S1・・
10 B S1  A S2  B  S2  E  S3・・
11 E S1  A S1  A  S2  B  S1・・
12 A S2  A S3  B  S1  A  S1・・
〜 ブロック1 ブロック2  ブロック3  ブロック4


90
ブロック1 N7:T12 7列:12行目 全体でN7:EI12 *14ブロックです
ブロックは、列に月度 行に名前 N7:EI12
   集計する場所は以下の部分です
        EP ER ES ET EU EV EW EX EY EZ
  7 E S1 S2 S3 S4 S5 S6 S7 S8 S9 (範囲N7:EI12 E S1の検索)ER7へ返す
    8 A S1 S2 S3 S4 S5 S6 S7 S8 S9
    9 B S1 S2 S3 S4 S5 S6 S7 S8 S9
    
表全体 N7:EI90 まで
       I列   J列
dateシート2 I3 E  J3 S1
       I4 A  J4 S2  
       I5 B  J5 S3
           〜
           〜
           J11 S9 まで 
 まだまだ十分に説明が出来ていないかも知れませんが宜しくお願いします。
 
この度は、誠にご無礼を致しまして申し訳御座いませんでした。

回答
投稿日時: 20/02/03 14:58:36
投稿者: Mike

Mike さんの引用:
結果は下記だけど、2、3行目の数値が異なるのは私が Sheet1 中のデータを1か所だけ転記ミスした所為かも
桃太郎7さんへ、
私がミスっていたのは、セル I1 でした。
B」であるべきところを「A」にしていたのです。
以上、ご報告まで。

回答
投稿日時: 20/02/03 15:34:10
投稿者: んなっと

検索範囲N7:EI12の時の式は、もう回答しましたよ。よく読んでください。

回答
投稿日時: 20/02/03 21:09:32
投稿者: んなっと

  EP EQ ER
 7 E S1 3
 8 B S1 5
 9 A S1 7
10 E S2 4
11 B S2 5
12 A S2 5
13 E S3 7
14 B S3 5
15 A S3 4
 
  ↓こっそりと下の形に変えましたね。あきれた。
 
 EP EQ ER ES ET
6    S1 S2 S3
7 E   3  1  7
8 A   7  3  4
9 B   5  2  5
 
ER7
=SUMPRODUCT((MOD(COLUMN(N$7:EH$200),7)=0)*(N$7:EH$200=$EP7)*(O$7:EI$200=ER$6))
右方向・→下方向・↓
 
または
=SUMPRODUCT((N$7:EH$200=$EP7)*(O$7:EI$200=ER$6))
 
さっさとスレッドを閉じてください。。

回答
投稿日時: 20/02/03 21:55:45
投稿者: takesi

わたしも集計勉強したいのですが
桃太郎7さん
シート作っていたら ブロック19って EJとEKになりませんか?
 
私間違ってますでしょうか?

回答
投稿日時: 20/02/03 23:20:42
投稿者: takesi

14ブロックなんですね
 
N7からT12を1ブロックとする
N7からDG12までの14ブロック(NからTで7列 7*14=98)
ブロック内(列に月度 行に名前)がどのように並んでいるのでしょうか
で何を集計したい(ブロック内に行列あって、横に14ブロックを集計したい?)のでしょうか?
表例と会わないような気がします。
 
今、んなっと様に解答いただいているのは
ブロック最初の2列(3列目から7列目のデータ含まない)で集計
行は200行まで含んでいる、だと思います。
下記は私が、14ブロックの2列目までに書き換えてみたものです。
=SUMPRODUCT((MOD(COLUMN(N$7:DA$200),7)=0)*(N$7:DA$200=$EP7)*(O$7:DB$200=ER$6))

回答
投稿日時: 20/02/04 10:47:26
投稿者: んなっと

式自体はよくあるパターンですが、質問者のシート上のセルの配置を読み解く部分が最も難しい。
配置が頻繁に変わっていて、今後もその可能性があります。
「やはりうまくいきません」
「伝わらなかったようなので、書き直します」(といってまた新たに配置を変える)
が続くかもしれないので、回答を控えた方がいいように思われますね。
 
スレッドを閉じてください。

投稿日時: 20/02/04 11:16:30
投稿者: 桃太郎7

うなっとさん 皆さん いろいろとご迷惑な質問で申し訳ありませんでした。
結果報告ですが、うなっとさんの解答をプリントして見直しをしましたら私が質問しました
結果を返す事が出来ました。
 
いろいろと ご親切にご指導頂きましてありがとうございました。