[[20240517105025]]　『xlookupでデータ抽出』（とんかつ）　ページの最後に飛ぶ

『xlookupでデータ抽出』（とんかつ）

こんにちは。
データ抽出で悩んでおり解決策が分らず、こちらの掲示板にたどり着きました。有識者のお知恵をお借りしたいです。

シート１のデータ参照元からデータを引っ張り、シート2へ抽出するのですが、条件としてセルに"‐"が入っている場合は、一つ上の値を、"‐"が入っていない場合はそのセルの値を抜き出したいと思っています。
Xlookup関数を使ってみたのですが上手くいきません。シート2のように抽出するにはどのような数式を組めばよいかご教授下さい。
何卒よろしくお願い致します。

シート１
A列 B列 C列 D列 E列

		4月	5月	6月	
東京	果実	100 	50 	30 	
		-	10 	-	
名古屋	野菜	5 	9 	3 	
		2 	-		
大阪	魚	10 	20 	1 	
		-	5 	3

シート２
A列 B列 C列 D列

	4月	5月	6月	
東京	100 	10 	30

名古屋 2 9 3

大阪 10 5 3

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

 -が2行以上続くこともあり得るのだろうか？
（ねむねむ） 2024/05/17(金) 11:14:41

ねむねむ様

ご返信ありがとうございます。
"-"が2行以上続くことはありません。
東京、名古屋、大阪の各項目に、"-"は1行だけです。
よろしくお願い致します。
（とんかつ） 2024/05/17(金) 11:43:10

 質問が続いてすまないが。
 各都市は必ず2行なのだろうか？
（ねむねむ） 2024/05/17(金) 11:57:30

シート２はなぜそのようになるんですか。
（000） 2024/05/17(金) 12:22:30

 B2
=LOOKUP(10^9,INDEX(シート１!C:C,XMATCH($A2,シート１!$A:$A)+{0,1}))
右方向・→下方向・↓
（んなっと） 2024/05/17(金) 12:40:50

解決したのかもしれませんが、書いてしまったので投稿しておきます。
横からですが、別表つくって求めるのではだめなんでしょうか？

    Sub test()
        Dim dstSH As Worksheet
        With Worksheets(1).Range("A1").CurrentRegion
            Set dstSH = Worksheets.Add(after:=.Parent)
            dstSH.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With

        With dstSH
            Intersect(.UsedRange, .UsedRange.Offset(1), .Range("A:B")).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
            Intersect(.UsedRange, .UsedRange.Offset(1, 2)).SpecialCells(xlCellTypeConstants, 2).FormulaR1C1 = "=R[-1]C"
            .Range("A12").Formula2 = "=UNIQUE(A2:A7)"
            .Range("B11").Formula2 = "=C1:E1"
            .Range("B12:B14").Formula2 = "=XLOOKUP(A12,$A$2:$A$7,C$2:E$7,,,-1)"
        End With
    End Sub

名古屋の6月が空白を参照して0になっちゃいますが、そこは表示形式でごまかすとして....

（もこな２） 2024/05/17(金) 12:45:54

ねむねむ様

説明不足で申し訳ございません。
２行になります。
ただし、シート１の都市名の一つ下にあるセルは空白となります。

んなっと様
数式のご提示ありがとうございます。
しかしながら、数式をコピーしたのですが上手くいきません。
初歩的な質問で恐縮ですが、数式の中にある(10^9はどういった意味でしょうか？

もこな２
vbaのコード提示ありがとうございます。
しかしながら、当方VBAの知識がゼロで理解が追い付いておりません。
せっかくご教授いただいたにも関わらず、誠に申し訳ございません。
（とんかつ） 2024/05/17(金) 13:22:03

 <Sheet1>
     __A___  __B_  __C  __D  __E
 1                 4月  5月  6月
 2   東京    果実  100   50   30
 3                 -     10  -  
 4   名古屋  野菜    5    9    3
 5                   2  -       
 6   大阪    魚     10   20    1
 7                 -      5    3

 <Sheet2>
     __A___  __B  __C  __D
 1           4月  5月  6月
 2   東京    100   10   30
 3   名古屋    2    9    3
 4   大阪     10    5    3

 [B2]  =LET(a,XLOOKUP(A2,Sheet1!A:A,Sheet1!C:E),b,OFFSET(a,1,0),IF(ISNUMBER(b),b,a))  ↓コピー

（まる2021） 2024/05/17(金) 14:05:49

んなっと様

スイマセン、私のコピペミスでした。ご提示いただいた数式で上手くいきました。
ありがとうございます。

まる2021様

数式のご提示ありがとうございます。
数式のコピペをしたのですが上手くいきませんでした。数式の中にあるaとかbの意味ななんでしょうか。

初歩的な質問で申し訳ございません。
（とんかつ） 2024/05/17(金) 14:18:53

＞当方VBAの知識がゼロで理解が～
いや、VBAを使うって話じゃなくて【別表】をつくって、そこから【XLOOKUP関数】で拾うんじゃだめか？って話だったのですが・・・・
（説明がめんどうだったので、別表を作る作業と数式を書き込む作業をコードにしただけです）

まぁ、解決したようなのでご放念ください。

（もこな２） 2024/05/17(金) 20:43:25

もこな２様

ご丁寧にありがとうございました。
今後ともどうぞ宜しくお願い致します。

まる2021様
ありがとうございました。
（とんかつ） 2024/05/20(月) 17:53:40

お久しぶりです、とんかつです。
まる2021様に教えて頂いた数式を現在使用しております。
B2セルに=LET(a,XLOOKUP(A2,Sheet1!A:A,Sheet1!C:E),b,OFFSET(a,1,0),IF(ISNUMBER(b),b,a))
さらに質問となり恐縮ですが、シート1の一行下にある値を取得した場合、
例）東京の10、名古屋の2、大阪の5と3

以下シート3に◯を表示させるにはどのような数式になるのかご教示願います。よろしくお願いします。
<Sheet3>

     __A___  __B  __C  __D
 1           4月  5月  6月
 2   東京          ◯  
 3   名古屋    ◯      
 4   大阪          ◯    ◯
（とんかつ） 2024/07/30(火) 21:13:08

 Sheet1の実際のデータ範囲が不明ですが
 例示通りだとして

 Sheet3
 B2 =IF(ISNUMBER(INDEX(Sheet1!C:C,SEQUENCE(3,1,3,2))),"○","")
 右コピー
 ※下にコピーする必要なし

 または
 B2 =IF(ISNUMBER(XLOOKUP(A2,Sheet1!$A$2:$A$6,Sheet1!$C$3:$E$7)),"○","")
 下コピー
 ※右にコピーする必要なし（上と逆）

 参考まで
（笑） 2024/07/31(水) 13:43:40

（笑）様

ありがとうございます。無事に解決いたしました。
今後とも宜しくお願い致します。
（とんかつ） 2024/07/31(水) 22:00:05

コメント返信：

[ 一覧(最新更新順) ]

[[20240517105025]] 『xlookupでデータ抽出』（とんかつ） ページの最後に飛ぶ

[[20240517105025]]　『xlookupでデータ抽出』（とんかつ）　ページの最後に飛ぶ