Excel (一般機能)

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

 
(macOS 12.0 : Microsoft 365)
データベースから日付順に特定の行と列の値を返したい。
投稿日時: 24/02/27 19:11:42
投稿者: per

シートA
出荷日    取引先コード    店舗コード    取引先/店舗コード    取引先名    届先名    商品コード    商品名    出荷総数量    取引先別単価    売上
2024/2/1    1    100    1-100    ぺんぺる    東京    AAA    くろいろ    100    100    10000
2024/2/1    1    100    1-100    ぺんぺる    東京    BBB    きいろ    300    100    30000
2024/2/1    2    101    2-101    こくを    神奈川    AAA    くろいろ    200    200    40000
2024/2/1    2    101    2-101    こくを    神奈川    CCC    あか    500    200    100000
2024/2/1    3    111    3-111    ズブラ    愛知    BBB    きいろ    200    300    60000
2024/2/1    3    111    3-111    ズブラ    愛知    CCC    あか    400    300    120000
2024/2/1    4    200    4-200    トンボる    千葉    AAA    くろいろ    500    400    200000
2024/2/1    4    200    4-200    トンボる    千葉    CCC    あか    200    400    80000
2024/2/2    1    100    1-100    ぺんぺる    東京    CCC    あか    300    100    30000
2024/2/2    1    100    1-100    ぺんぺる    東京    DDD    みどり    600    100    60000
2024/2/2    2    101    2-101    こくを    神奈川    DDD    みどり    1000    200    200000
2024/2/2    2    101    2-101    こくを    神奈川    EEE    だいだい    400    200    80000
2024/2/2    3    111    3-111    ズブラ    愛知    AAA    くろいろ    600    300    180000
2024/2/2    3    111    3-111    ズブラ    愛知    BBB    きいろ    700    300    210000
2024/2/2    4    200    4-200    トンボる    千葉    BBB    きいろ    300    400    120000
2024/2/2    4    200    4-200    トンボる    千葉    DDD    みどり    900    400    360000
 
※行
項目行 1
以下 2〜
※列
出荷日 A 以降右項目毎にB〜C〜D...
 

引用:
上記のデータベースから
取引先コードを元に以下の項目を抽出したい。
※項目が多いため位置ズレご容赦ください

 
シートB
取引先コード    1    (A1:B1)                
    A        B C D E F G                                             
1 出荷日    届先名        商品コード    商品名    出荷総数量    取引先別単価    売上
2 2024/2/1 東京        AAA    くろいろ    100    100    10000
3 2024/2/1     東京        BBB    きいろ    300    100    30000
4 2024/2/2     東京        CCC    あか    300    100    30000
5 2024/2/2     東京        DDD    みどり    600    100    60000
        
シートC                
取引先コード    2    (A1:B1)                    
    A        B C D E F G                 
1 出荷日    届先名    商品コード    商品名    出荷総数量    取引先別単価    売上
2 2024/2/1     神奈川        AAA    くろいろ    200    200    40000
3 2024/2/1     神奈川        CCC    あか    500    200    100000
4 2024/2/2     神奈川        DDD    みどり    1000    200    200000
5 2024/2/2     神奈川        EEE    だいだい    400    200    80000
 
※実際の列、行番号
取引先コード D6
出荷日 B23    
届先名     C23
商品コード    I23
商品名    J23
出荷総数量 N23    
取引先別単価    O23
売上 Q23
以下24,25,26~とデータベースからの抽出行が続く

回答
投稿日時: 24/02/27 19:31:44
投稿者: んなっと

Macは持っていないので、使えるかどうかわかりませんが...

引用:
※実際の列、行番号
取引先コード D6
出荷日 B23
届先名 C23
商品コード I23
商品名 J23
出荷総数量 N23
取引先別単価 O23
売上 Q23
以下24,25,26~とデータベースからの抽出行が続く
↑この部分は完全に無視し、例示された表(↓)のレイアウトで回答します。
 
       A    B      C     D      E       F   G
1 取引先コード    1                            
2    出荷日 届先名 商品コード  商品名 出荷総数量 取引先別単価  売上
3   2024/2/1  東京     AAA くろいろ     100      100 10000
4   2024/2/1  東京     BBB  きいろ     300      100 30000
5   2024/2/2  東京     CCC   あか     300      100 30000
6   2024/2/2  東京     DDD  みどり     600      100 60000
 
A3
=CHOOSECOLS(FILTER(A!A2:K500,A!B2:B500=B1),XMATCH(A2:G2,A!1:1))

回答
投稿日時: 24/02/27 20:34:24
投稿者: んなっと

こんな式も。
 
       A    B      C     D      E       F   G
1 取引先コード    1                            
2    出荷日 届先名 商品コード  商品名 出荷総数量 取引先別単価  売上
3   2024/2/1  東京     AAA くろいろ     100      100 10000
4   2024/2/1  東京     BBB  きいろ     300      100 30000
5   2024/2/2  東京     CCC   あか     300      100 30000
6   2024/2/2  東京     DDD  みどり     600      100 60000
 
A3
=IFERROR(INDEX(A!$1:$500,AGGREGATE(15,6,ROW($2:$500)/(A!$B$2:$B$500=$B$1),ROW(A1)),MATCH(A$2,A!$1:$1,0)),"")
右方向・→下方向・↓
A列の表示形式:日付

投稿日時: 24/02/27 20:43:06
投稿者: per

んなっと さんの引用:
Macは持っていないので、使えるかどうかわかりませんが...
引用:
※実際の列、行番号
取引先コード D6
出荷日 B23
届先名 C23
商品コード I23
商品名 J23
出荷総数量 N23
取引先別単価 O23
売上 Q23
以下24,25,26~とデータベースからの抽出行が続く
↑この部分は完全に無視し、例示された表(↓)のレイアウトで回答します。
 
       A    B      C     D      E       F   G
1 取引先コード    1                            
2     出荷日 届先名 商品コード  商品名 出荷総数量 取引先別単価  売上
3   2024/2/1  東京     AAA  くろいろ     100      100 10000
4   2024/2/1  東京     BBB   きいろ     300      100 30000
5   2024/2/2  東京     CCC    あか     300      100 30000
6   2024/2/2  東京     DDD   みどり     600      100 60000
 
A3
=CHOOSECOLS(FILTER(A!A2:K500,A!B2:B500=B1),XMATCH(A2:G2,A!1:1))

 
 
んなっと 様
 
ありがとうございます。
例では綺麗にスピルできたのですが、
 
実際の列では、レイアウトの関係で
項目の間に以下のようなブランクがあり、
B3=#VALUE!となってしまいました。
 

       B    C      I     J      N      O    Q            
23    出荷日  届先名   商品コード 商品名 出荷総数量 取引先別単価  売上 
24   2024/2/1  東京     AAA  くろいろ   100     100  10000 
25   2024/2/1  東京     BBB   きいろ   300     100  30000 
26   2024/2/2  東京     CCC    あか   300     100  30000 
27   2024/2/2  東京     DDD   みどり   600     100  60000

ブランク列 DEFGH KLM P
結合はなし

 

 
 
 
 

回答
投稿日時: 24/02/27 20:58:36
投稿者: んなっと

      B       C D E F G H      I     J
 6      取引先コード 1                 
  
23  出荷日    届先名         商品コード  商品名
24 2024/2/1     東京            AAA くろいろ
25 2024/2/1     東京            BBB  きいろ
26 2024/2/2     東京            CCC   あか
27 2024/2/2     東京            DDD  みどり
 
B24
=FILTER(INDEX(A!2:500,,XMATCH(B23,A!1:1)),A!$B2:$B500=$D6)
→B24をコピーして C24,I24,J24,...に貼り付け

投稿日時: 24/02/27 21:04:19
投稿者: per

んなっと さんの引用:
こんな式も。
 
       A    B      C     D      E       F   G
1 取引先コード    1                            
2    出荷日 届先名 商品コード  商品名 出荷総数量 取引先別単価  売上
3   2024/2/1  東京     AAA くろいろ     100      100 10000
4   2024/2/1  東京     BBB  きいろ     300      100 30000
5   2024/2/2  東京     CCC   あか     300      100 30000
6   2024/2/2  東京     DDD  みどり     600      100 60000
 
A3
=IFERROR(INDEX(A!$1:$500,AGGREGATE(15,6,ROW($2:$500)/(A!$B$2:$B$500=$B$1),ROW(A1)),MATCH(A$2,A!$1:$1,0)),"")
右方向・→下方向・↓
A列の表示形式:日付

 
 
んなっと 様
 
↑こちらで解答を得ました!お見事です!とても気持ちが良いです^^
 
式はさっぱりなので、
返答を持っても理解が及ばないとは思いつつ
1つ質問いいでしょうか?
 
ROW(A1) こちらはA1を参照しているのでしょうか?
右や下へコピーすると全く意味のない場所を参照しています。
この手の関数でよく出てくるのですが、
何を意味しているのでしょうか?
 

投稿日時: 24/02/27 21:13:50
投稿者: per

んなっと さんの引用:
      B       C D E F G H      I     J
 6      取引先コード 1                 
  
23  出荷日    届先名         商品コード  商品名
24 2024/2/1     東京            AAA くろいろ
25 2024/2/1     東京            BBB  きいろ
26 2024/2/2     東京            CCC   あか
27 2024/2/2     東京            DDD  みどり
 
B24
=FILTER(INDEX(A!2:500,,XMATCH(B23,A!1:1)),A!$B2:$B500=$D6)
→B24をコピーして C24,I24,J24,...に貼り付け

 
こちらでもスパッと!ありがとうございます。
 
なるべく重くならないデータ管理をしたい場合に、
どちらが理想でしょうか?
 
 
またデータベースから売上の値を引っ張ってくるのと、
数量と単価で計算式を求めるのとではどちらが負担ないでしょう?

投稿日時: 24/02/29 12:47:21
投稿者: per

んなっと 様
 
ありがとうございましたm(._.)m