[[20230816093913]]　『検索』（もち）　ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

　

『検索』（もち） 

  A   B  C 　D　  E      F       G　     H　
1                       番号で入力						
2 A   1  子              3月11日 3月2日  3月3日			
3     2  丑　　　いち    1       10      2			
4 社  3  寅　　　に     11       12      1			
5     4          さん   13       15      3			
6 B  10  子								
7 社 11  丑              3月1日  3月2日  3月3日	
8    12  寅      いち     1子　　10子    2丑
9    13  卯      に      11丑    12寅    1子
10   14  辰      さん    13卯    15巳    3寅
11   15  巳 							
15   16	                3月1日					
13                      A社      B社		
14                      1子     11丑	
15                              13卯	
上記のような表があります。

1,日付ごとにF3から番号で入力する。
2,AF7に集計で
3,F15の日付で検索したら、AとBとに結果がでてくる。
ご教授願います。

＜ 使用 Excel：Microsoft365、使用 OS：Windows10 ＞ 

 仕様がよく分からないです。取り敢えず、たたき台。


 (1) F14セル =IFERROR(TEXTSPLIT(TEXTJOIN("-",TRUE,LET(r,XLOOKUP(F12,F2:AJ2,F3:AJ5),IF(r< 10,r&XLOOKUP(r,B2:B12,C2:C12),""))),,"-"),"")
 (2) G14セル =IFERROR(TEXTSPLIT(TEXTJOIN("-",TRUE,LET(r,XLOOKUP(F12,F2:AJ2,F3:AJ5),IF(r>=10,r&XLOOKUP(r,B2:B12,C2:C12),""))),,"-"),"")


 <結果図>
 行  ___F___  __G__
 12   3月1日       
 13  A社      B社  
 14  1子      11丑 
 15           13卯

（半平太） 2023/08/16(水) 11:02:08

E7〜K10は表示です。
うまいこといきません。
（もち） 2023/08/16(水) 11:32:52 
 ＞E7〜K10は表示です。


 とは、そこの範囲も数式で出したいってことなんですか？


 E7セル =HSTACK(E2:E5&"",VSTACK(IFERROR((F2:K2&"")*1,""),F3:K5&XLOOKUP(F3:K5,B2:B12,C2:C12,"")))

（半平太） 2023/08/16(水) 12:12:04

ご返答感謝します。
やってみましたが、F14,G14は空白で値が出ません。
E7は
＃NAME？
というエラー表示がでる。
この数式は、理解するまで時間かかりそうです。

（もち） 2023/08/16(水) 13:18:45 

 お役に立てず、済みませーん。


 他の回答者のレスをお待ちください。

（半平太） 2023/08/16(水) 13:22:37

誰か助けてください。
（もち） 2023/08/17(木) 13:53:39
半平太さんの回答で解決したとおもっていたので、投稿を控えていましたが、未解決のようなので研究成果発表。

▼処理前 

    ___A__   _B_   __C_ ...  ____E___   ____F___   ____G___  ____H___    _____J_____
  1 会社名   No.   分類        番号           番号で入力
  2   A社     1     子                   3月1日     3月2日    3月3日
  3   A社     2     丑         いち         1         10         2
  4   A社     3     寅         に          11         12         1
  5                            さん        13         15         3
  6   B社    10     子
  7   B社    11     丑                   3月1日     3月2日    3月3日
  8   B社    12     寅         いち        ★
  9   B社    13     卯         に
 10   B社    14     辰         さん
 11   B社    15     巳
 12                           3月1日　　　　　　　　　　　　　　　　　　　作業エリア
 13                             ◆                                            ♪
 14                             ◇ →(フィル)コピー                           ☆
 15


 ★ F8セル  =XLOOKUP(F3:H5,B2:B12,B2:B12&C2:C12)
 ◆ E13セル =TRANSPOSE(UNIQUE(XLOOKUP(INDEX(F3:H5,,MATCH(E12,F2:H2,0)),B2:B12,A2:A12)))
 ◇ E14セル =IF(E13="","",FILTER(INDEX($F$8#,,MATCH($E$12,$F$7:$H$7,0)),INDEX($J$14#,,MATCH(E13,$J$13#,0)),""))　
 ♪ J13セル =TRANSPOSE(UNIQUE(XLOOKUP(INDEX(F3:H5,,MATCH(E12,F2:H2,0)),B2:B12,A2:A12)))
 ☆ J14セル =XLOOKUP(INDEX(F3:H5,,MATCH(E12,F2:H2,0)),B2:B12,A2:A12)=J13#

▼結果 

    ___A__   _B_   __C_ ...  ____E___   ____F___   ____G___  ____H___    _____J_____    ___K___
  1 会社名   No.   分類        番号           番号で入力
  2   A社     1     子                   3月1日     3月2日    3月3日
  3   A社     2     丑         いち         1         10         2
  4   A社     3     寅         に          11         12         1
  5                            さん        13         15         3
  6   B社    10     子
  7   B社    11     丑                   3月1日     3月2日    3月3日
  8   B社    12     寅         いち       1子        10子      2丑
  9   B社    13     卯         に        11丑        12寅      1子
 10   B社    14     辰         さん      13卯        15巳      3寅
 11   B社    15     巳
 12                           3月1日　　　　　　　　　　　　　　　　　　　作業エリア
 13                            A社        B社　　　　　　　　　　　　　      A社          B社
 14                            1子       11丑                               TRUE         FALSE
 15                                      13卯                               FALSE        TRUE
 16                                                                         FALSE        TRUE

（もこな２） 2023/08/17(木) 16:25:53

ご回答ありがとうございます。

時間のある時に試して頑張ってみます。
すごい！！助かります。
（もち） 2023/08/18(金) 11:13:31

ご回答ありがとうございます。
やってみましたすごい！！
こんな関数がわかりづらい。
勉強になります。
お願い！！
F3が入力してなければ、F8も空白であれば助かる。
F14の結果も合計として、表示できれば助かる。
F３からF5の入力がすべて１であれば、F14の結果は１子が横のセルに3個と表示

出来たらうれしいです。

（もち） 2023/08/18(金) 13:25:49

＞F3が入力してなければ、F8も空白であれば助かる。
＞F14の結果も合計として、表示できれば助かる。
理解できないので【行・列】を踏まえたレイアウト（処理前、希望する結果）で説明してください。

＞F3からF5の入力がすべて１であれば、F14の結果は１子が横のセルに3個と表示
【1子】ではなく、たとえば【1_子】のように「分類」「No.」に分けられるようにしておけば、SUMIFS関数で対処できるのでは？

（もこな２） 2023/08/18(金) 15:03:41

  ___A__   _B_   __C_ ...  ____E___   ____F___   ____G___  ____H___    _____J_____    ___K___
  1 会社名   No.   分類        番号           番号で入力
  2   A社     1     子                   3月1日     3月2日    3月3日
  3   A社     2     丑         いち         1                    2
  4   A社     3     寅         に          11                    1
  5                            さん        13         15         3
  6   B社    10     子
  7   B社    11     丑                   3月1日     3月2日    3月3日
  8   B社    12     寅         いち       1子                  2丑
  9   B社    13     卯         に        11丑                  1子
 10   B社    14     辰         さん      13卯        15巳      3寅
 11   B社    15     巳
 12                           3月2日　　　　　　　　　　　　　　　　　　　作業エリア
 13                            A社        B社　　　　　　　　　　　　　      A社          B社              該当なし
 14                                      15巳x1                             TRUE         FALSE
 15                                      　　                               FALSE        TRUE
 16                                                                         FALSE        TRUE
（もち） 2023/08/18(金) 15:28:40
___A__ _B_ __C_ ... ____E___ ____F___ ____G___ ____H___ _____J_____ ___K___ 
  1 会社名   No.   分類        番号           番号で入力
  2   A社     1     子                   3月1日     3月2日    3月3日
  3   A社     2     丑         いち         1                    2
  4   A社     3     寅         に          11         15         1
  5                            さん        13         15         3
  6   B社    10     子
  7   B社    11     丑                   3月1日     3月2日    3月3日
  8   B社    12     寅         いち       1子                  2丑
  9   B社    13     卯         に        11丑        15巳      1子
 10   B社    14     辰         さん      13卯        15巳      3寅
 11   B社    15     巳
 12                           3月2日　　　　　　　　　　　　　　　　　　　
 13                            A社        B社　　　　
 14　　　　　　　　　          該当なし　 15巳x2

上記のような感じです。
入力は
・G3=空白
結果が
・G8=空白
・E14=該当なし
・F14=15巳x2

です。
スミマセン
分かれば苦労しないのですが！！
（もち） 2023/08/18(金) 15:45:34

G3が空白であれば、G8も空白にすることができました。
E13:F14の検索値の値が＃N/Aになる。
むつかしい。

誰か教えてください。
（もち） 2023/08/21(月) 16:00:38

「助けて」「教えて」ばっかりでなく、
少しは自分で「考えろ」「調べろ」！

（他力本願） 2023/08/21(月) 16:42:16

たまにいるよね！！
こうゆうひと
困っているんでしょ。
（だめ） 2023/08/22(火) 10:49:02
その後回答がつかないようなので、自分も研究成果発表します。
ただこちらExcel2007なので古臭いかもです。
かつ、もこな2さんと同様に作業エリアが必要です。
それでも良ければ参考にしてください。 

   　|[A]   |[B]|[C] |[D]|[E]     |[F]       |[G]   |[H]   |[I]|[J]       |[K]|[L]
 [1] |会社名|No.|分類|   |番号    |番号で入力|      |      |   |          |   |   
 [2] |A社   |  1|子  |   |        |3月1日    |3月2日|3月3日|   |          |   |   
 [3] |      |  2|丑  |   |いち    |         1|      |     2|   |          |   |   
 [4] |      |  3|寅  |   |に      |        11|    15|     1|   |          |   |   
 [5] |      |   |    |   |さん    |        13|    15|     3|   |          |   |   
 [6] |B社   | 10|子  |   |        |          |      |      |   |          |   |   
 [7] |      | 11|丑  |   |        |3月1日    |3月2日|3月3日|   |          |   |   
 [8] |      | 12|寅  |   |いち    |1子       |      |2丑   |   |          |   |   
 [9] |      | 13|卯  |   |に      |11丑      |15巳  |1子   |   |          |   |   
 [10]|      | 14|辰  |   |さん    |13卯      |15巳  |3寅   |   |          |   |   
 [11]|      | 15|巳  |   |        |          |      |      |   |          |   |   
 [12]|      |   |    |   |3月2日  |          |      |      |   |作業エリア|   |   
 [13]|      |   |    |   |A社     |B社       |      |      |   |G8:G10    |   |   
 [14]|      |   |    |   |該当なし|15巳×2   |      |      |   |G3        |   |   
 [15]|      |   |    |   |        |          |      |      |   |G4        |  2|B社
 [16]|      |   |    |   |        |          |      |      |   |G5        |   |

F7 =F$2
　　H7まで横にコピー
F8 =IFERROR(F3&INDEX($C$2:$C$11,MATCH(F3,$B$2:$B$11,0),),"")
　　F10まで縦にコピー、そのままH10まで横にコピー

作業エリア
J13 =ADDRESS(8,MATCH($E$12,$2:$2,0),4)&":"&ADDRESS(10,MATCH($E$12,$2:$2,0),4)
J14 =ADDRESS(ROW(J3),MATCH($E$12,$2:$2,0),4)
　　J16まで縦にコピー
K14 =IF(INDIRECT($J14)="","",COUNTIF(INDIRECT($J14&":"&$J$16),INDIRECT($J14)))
K15 =IF(INDIRECT($J15)="","",IF(COUNTIF(INDIRECT($J$14&":"&$J15),INDIRECT($J15))>1,"",
COUNTIF(INDIRECT($J15&":"&$J$16),INDIRECT($J15))))
　　K16まで縦にコピー
L14 =IF($K14="","",IF(INDIRECT(J14)<=3,$E$13,$F$13))
　　L16まで縦にコピー

E14 =IFERROR(IF(INDEX($K$14:$K$16,1/LARGE(INDEX(($L$14:$L$16=E$13)/ROW($1:$3),),ROW(E1)),)>1,
INDEX(INDIRECT($J$13),1/LARGE(INDEX(($L$14:$L$16=E$13)/ROW($1:$3),),ROW(E1)),)&"×"
&INDEX($K$14:$K$16,1/LARGE(INDEX(($L$14:$L$16=E$13)/ROW($1:$3),),ROW(E1)),),
INDEX(INDIRECT($J$13),1/LARGE(INDEX(($L$14:$L$16=E$13)/ROW($1:$3),),ROW(E1)),)),"該当なし")
　　F14まで横にコピー
E15 =IFERROR(IF(INDEX($K$14:$K$16,1/LARGE(INDEX(($L$14:$L$16=E$13)/ROW($1:$3),),ROW(E2)),)>1,
INDEX(INDIRECT($J$13),1/LARGE(INDEX(($L$14:$L$16=E$13)/ROW($1:$3),),ROW(E2)),)&"×"
&INDEX($K$14:$K$16,1/LARGE(INDEX(($L$14:$L$16=E$13)/ROW($1:$3),),ROW(E2)),),
INDEX(INDIRECT($J$13),1/LARGE(INDEX(($L$14:$L$16=E$13)/ROW($1:$3),),ROW(E2)),)),"")
　　E16まで縦にコピー、そのままF16まで横にコピー

「こんな関数がわかりづらい。」が妙に面白かったです。
以上。
（トルネコ） 2023/08/24(木) 00:04:38

＞こんな関数がわかりづらい。
それは貴方が「そういう仕様にしてください」と言っているもんだから仕方ないよね。

（hoti） 2023/08/24(木) 11:28:33

コメント返信：

[ 一覧(最新更新順) ]

YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.