元データ
1 2 4 2 2 2 3 3 3 2 1
A B C D E F G H I J K
から、以下のように、
1を入力すると、A と K が、
3を入力すると、G と H と I が右のセルに表示される、といったものを
作成しているのですが、なかなかうまくいきません・・・。
↓入力データ
1 A K
2 B D E F J
3 G H I
4 C
↑ ↑ここから右、HLOOKUPで引っ張れないデータ
↑HLOOKUPで引っ張れるデータ
HLOOKUP関数を駆使すれば、なんとかなりそうな気もするのですが・・・
どなたかご教授願えないでしょうか??
No.9ベストアンサー
- 回答日時:
#3です。
ん~っと、私の式はデータや表示位置が違う場合もあらかじめ考慮していたつもりなんですが…。
私が提示した式は
B5:=IF(COLUMN()-COLUMN($A5)>COUNTIF($A$1:$K$1,$A5),"",INDEX($A$2:$K$2,MATCH(LARGE(($A$1:$K$1=$A5)/COLUMN($A$1:$K$1),COLUMN()-COLUMN($A5)),1/COLUMN($A$1:$K$1),0)))
でしたね
・$A5 ⇒ $E43
・$A$1:$K$1 ⇒ $D$1:$N$1
・$A$2:$K$2 ⇒ $D$4:$N$4
と置き換えるだけです。(Excel2003で動作確認済)
F43:=IF(COLUMN()-COLUMN($E43)>COUNTIF($D$1:$N$1,$E43),"",INDEX($D$4:$N$4,MATCH(LARGE(($D$1:$N$1=$E43)/COLUMN($D$1:$N$1),COLUMN()-COLUMN($E43)),1/COLUMN($D$1:$N$1),0)))
他の方の式については、データや表示位置がA列からあることを前提にしていらっしゃるようなので、
直接数字を差し引きして調整する必要があるかと思います。
ありがとうございます!無事、出来ました!
※置き換えしていたのですが、今、式を見直してみたら
置き換えに漏れがありました・・・。
当方の初歩的なミスのせいで お手数をおかけしてしまい、
申し訳ありませんでした。
No.11
- 回答日時:
>¥=A44を例にとると、「B,D,E,F,J」と表示させたい
★F44ではないでしょうか?
F44=IF(COLUMN(A1)>COUNTIF($D$1:$N$1,$E43),"",INDEX($D$4:$N$4,SMALL(INDEX(SUBSTITUTE(($D$1:$N$1=$E43)*1,0,9^9)*COLUMN($A:$K),),COLUMN(A1))))
★右と下にコピー
No.10
- 回答日時:
[ANo.8この回答への補足]に対するコメント、
1.セル F43 に次の配列数式を入力して、Shift+Ctrl+Enterで確定
(数式バー上では式の左端と右端にそれぞれ { と } が付加される)
=OFFSET($D$4,0,SMALL(IF($D$1:$N$1=$E43,COLUMN($A1:$K1),""),COLUMN(A1))-1)
2.同セルに次の[条件付き書式]を設定
数式が =ISERROR(F43)
フォント色 白
3.セル F43 を右方にズズーッと(セル P43 まで)ドラッグ&ペースト
4.範囲 F43:P43を下方にズズーッとドラッグ&ペースト
No.8
- 回答日時:
元データ(Sheet1)
A B C D E F G H I J K
1 1 2 4 2 2 2 3 3 3 2 1
2 A B C D E F G H I J K
入力データ(Sheet2)
A B C D E F G H I J K L
1 1 A K
2 2 B D E F J
3 3 G H I
4 4 C
1.Sheet2 のセル B1 に次の配列数式を入力して、Shift+Ctrl+Enterで確定
(数式バー上では式の左端と右端にそれぞれ { と } が付加される)
=OFFSET(Sheet1!$A$2,0,SMALL(IF(Sheet1!$A$1:$K$1=$A1,COLUMN($A1:$K1),""),COLUMN(A1))-1)
2.同セルに次の[条件付き書式]を設定
数式が =ISERROR(B1)
フォント色 白
3.同セルを[コピー]して範囲 B1:L4 に[貼り付け]
この回答への補足
ご回答ありがとうございます!
これを、実際のデータ位置に置き換えて反映させてみようとしたのですが、
うまくいきませんでした・・・。
実際の元データの位置は、
D1~N1と、D4~N4で、
参照のためのデータを入力する位置は、
E43~、
データ表示位置は、
F43~
となります。
(以下参照)
A B C D E F G H I J K L M N
1 @ @ @ 1 2 4 2 2 2 3 3 3 2 1
2 @ @ @ @ @ @ @ @ @ @ @ @ @ @
3 @ @ @ @ @ @ @ @ @ @ @ @ @ @
4 @ @ @ A B C D E F G H I J K
5
6
7
8
:
43 \ @ @ @ 1 A K
44 \ @ @ @ 2 B D E F J
45 \ @ @ @ 3 G H I
46 \ @ @ @ 4 C
47 \ @ @ @ 5
48 \ @ @ @ 6
49 \ @ @ @ 7
50 \ @ @ @ 8
:
@=任意のデータ
¥=A44を例にとると、「B,D,E,F,J」と表示させたい。
申し訳ありませんが、引き続きご教授願えないでしょうか?
No.7
- 回答日時:
関数の質問でしたが、難しくて挫折しました。
VBAで作ってみましたので、ご参考までに
1)データが入力されると該当する値を横に表示する例
2)新規シートにデータを並べ替えてしまう例
注)いずれも、元データはA1セルを基点に入力されている前提です。
1)の例 <A5セルに入力データを書き込む場合です。>
'==============ワークシート・モジュールに記述===================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long
If Target.Address <> "$A$5" Then Exit Sub
Range(Target.Offset(, 1), Cells(Target.Row, Columns.Count)).ClearContents
For c = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
If Cells(1, c).Value = Target.Value Then
Cells(Target.Row, Columns.Count).End(xlToLeft).Offset(, 1).Value = Cells(2, c).Value
End If
Next c
End Sub
2)の例 (新規シートに並べ替えた一覧表を表示する例です。)
'=================標準モジュールに記述==========================
Sub test()
Dim r As Long
Dim LastRow_A As Long
Dim LastCol_r As Long
Dim LastCol_1 As Long
Application.ScreenUpdating = False
'データを新規シートに行列変換して貼り付け
Range("A1").CurrentRegion.Copy
Sheets.Add
Range("A1").PasteSpecial Transpose:=True
'番号順に並べ替え
Columns("A:B").Sort Key1:=Range("A2"), Order1:=xlAscending
'同一番号データを横方向へ移動(複数列にも対応)
LastCol_1 = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow_A = Cells(Rows.Count, "A").End(xlUp).Row
For r = LastRow_A To 2 Step -1
LastCol_r = Cells(r, Columns.Count).End(xlToLeft).Column
If Range("A" & r).Value = Range("A" & r - 1).Value Then
Range("A" & r).Resize(, LastCol_r - 1).Offset(, 1).Copy _
Destination:=Cells(r - 1, LastCol_1 + 1)
Rows(r).Delete
End If
Next r
Application.ScreenUpdating = True
End Sub
No.6
- 回答日時:
◆こんな方法もありますよ(少し、式が短くなりました)
B5=IF(COLUMN(A1)>COUNTIF($A$1:$K$1,$A5),"",INDEX($A$2:$K$2,SMALL(INDEX(SUBSTITUTE(($A$1:$K$1=$A5)*1,0,9^9)*COLUMN($A:$K),),COLUMN(A1))))
★右と下にコピー
この回答への補足
ご回答ありがとうございます!
これを、実際のデータ位置に置き換えて反映させてみようとしたのですが、
うまくいきませんでした・・・。
実際の元データの位置は、
D1~N1と、D4~N4で、
参照のためのデータを入力する位置は、
E43~、
データ表示位置は、
F43~
となります。
(以下参照)
A B C D E F G H I J K L M N
1 @ @ @ 1 2 4 2 2 2 3 3 3 2 1
2 @ @ @ @ @ @ @ @ @ @ @ @ @ @
3 @ @ @ @ @ @ @ @ @ @ @ @ @ @
4 @ @ @ A B C D E F G H I J K
5
6
7
8
:
43 \ @ @ @ 1 A K
44 \ @ @ @ 2 B D E F J
45 \ @ @ @ 3 G H I
46 \ @ @ @ 4 C
47 \ @ @ @ 5
48 \ @ @ @ 6
49 \ @ @ @ 7
50 \ @ @ @ 8
:
@=任意のデータ
¥=A44を例にとると、「B,D,E,F,J」と表示させたい。
申し訳ありませんが、引き続きご教授願えないでしょうか?
No.5
- 回答日時:
◆少し、式が長いですがこんな方法もありますよ
A B C D E F G H I J K
1 1 2 4 2 2 2 3 3 3 2 1
2 A B C D E F G H I J K
3
4
5 1 A K
6 2 B D E F J
7 3 G H I
8 4 C
B5=IF(COLUMN(A1)>COUNTIF($A$1:$K$1,$A5),"",INDEX($A$2:$K$2,SUMPRODUCT(LARGE(($A$1:$K$1=$A5)*(COLUMN($A$1:$K$1)),1+COUNTIF($A$1:$K$1,$A5)-COLUMN(A1)))))
No.4
- 回答日時:
=HLOOKUP(INDIRECT("A"&ROW()),A$1:A$2,2,0)
と入力してフィルハンドルを右方向にL列までドラッグします。
そのまま下方向にドラッグします。
該当のないセルには「#N/A」とエラー値が表示されます。
「編集」-「ジャンプ」で「セル選択」ボタンを押し、
[数式]を選択して、[エラー値]以外のチェックをはずします。
[エラー値]の入ったセルが選択されます。
右クリックで「削除」で「左方向にシフト」を選択して「OK」ボタンを押します。
コピーして「形式を選択して貼り付け」で[値]のみ貼り付けます。
先に[値]のみ貼り付けた場合は、
[定数]を選択して、[エラー値]以外のチェックをはずします。
ちょっと面倒ですが、HLOOKUP関数を使ってやるならこんな方法でできそうです。
No.3
- 回答日時:
HLOOKUPは"最初にHITしたもの"しか返しませんから、
2番目にHITしたもの、3番目にHITしたもの、というふうに返すのは、
HLOOKUPをどう駆使しても難しいと思われます。
#2さんがおっしゃるように、まずユニークなコードを生成してそれを使って引くのが大原則です。
例えば、元のデータがA1から1行目と2行目に、「入力データ」の部分がA5から下方に入っているとして、
まず、
A3:=COUNTIF($A1:A1,A1)&"-"&A1
として右方にフィル。これが「N番目の□」というユニークなコードになります。
次に、
B5:=IF(COUNTIF($A$1:$K$1,$A5)<COLUMN()-COLUMN($A$5),"",INDEX($A$2:$K$2,MATCH(COLUMN()-COLUMN($A5)&"-"&$A5,$A$3:$K$3,0)))
として、下方・右方にフィルすればご要望のような結果が返ります。
略解
・自セルの列位置N【COLUMN()-COLUMN($A5)】を取得する。
・第1行について、検索値と等しい値がNより少なければ空白を返す。
・Nと検索値を組み合わせて検索用のコードを生成する。
・そのコードを第3行から探して位置Mを取得する。
・第2行について、M番目の値を返す。
一発でやろうとするならば、
B5:=IF(COLUMN()-COLUMN($A5)>COUNTIF($A$1:$K$1,$A5),"",INDEX($A$2:$K$2,MATCH(LARGE(($A$1:$K$1=$A5)/COLUMN($A$1:$K$1),COLUMN()-COLUMN($A5)),1/COLUMN($A$1:$K$1),0)))
を配列数式として入力し、下方・右方にフィル。
※通常の数式は、数式を入力した後Enterキーで確定しますが、
これは配列数式なので、CtrlとShiftを押しながらEnterで確定してください。
略解
・第1行について、検索値と等しければ列番号の逆数、でなければ0を返した配列を生成する。
・それらのうち、N番目に大きい値の位置Mを検索する。
後は上記と一緒です。
この回答への補足
ご回答ありがとうございます!
これを、実際のデータ位置に置き換えて反映させてみようとしたのですが、
うまくいきませんでした・・・。
実際の元データの位置は、
D1~N1と、D4~N4で、
参照のためのデータを入力する位置は、
E43~、
データ表示位置は、
F43~
となります。
(以下参照)
A B C D E F G H I J K L M N
1 @ @ @ 1 2 4 2 2 2 3 3 3 2 1
2 @ @ @ @ @ @ @ @ @ @ @ @ @ @
3 @ @ @ @ @ @ @ @ @ @ @ @ @ @
4 @ @ @ A B C D E F G H I J K
5
6
7
8
:
43 \ @ @ @ 1 A K
44 \ @ @ @ 2 B D E F J
45 \ @ @ @ 3 G H I
46 \ @ @ @ 4 C
47 \ @ @ @ 5
48 \ @ @ @ 6
49 \ @ @ @ 7
50 \ @ @ @ 8
:
@=任意のデータ
¥=A44を例にとると、「B,D,E,F,J」と表示させたい。
申し訳ありませんが、引き続きご教授願えないでしょうか?
No.2
- 回答日時:
>HLOOKUP関数を駆使すれば、なんとかなりそうな気もするのですが・・・
思想として、コード入力をすると表から対応するものをもってくるというのは、
コードがユニークであることが前提だと思うのです。
ですので、もとのデータが、例えば3のセルの下に、GHIと3文字入っていないと引っ張れないのではないでしょうか?
もし、この表で解決するとしたら
3列目は参照する元の表を、2列目のデータをピックアップしたところから右側にずらすという操作が必要ではないでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/02/10 17:07
- Excel(エクセル) エクセルの数式で教えてください。 5 2023/02/10 15:11
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/02/09 14:54
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 2 2022/06/25 22:42
- Excel(エクセル) エクセルデータの集計、一つのセルに複数のデータがある場合 7 2022/12/28 20:19
- Excel(エクセル) エクセルのマクロについて教えてください。 2 2023/02/11 14:50
- Visual Basic(VBA) 【VBA】データを入力後に,同一シート内に履歴として転記するVBAコードを教えていただきたいです。 3 2022/11/16 01:37
- Visual Basic(VBA) VBA ドロップダウンリストを残して値のみクリア 2 2022/10/27 05:42
- C言語・C++・C# C言語プログラム変更 2 2022/12/21 15:03
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Access レポート印刷するときに...
-
エクセルのデータをアクセスに...
-
access2021 メッセージボックス...
-
ACCESS VBA でのエラー解決の根...
-
CSVファイルの「0落ち」にVBA
-
Accessのクエリの結果を、既存...
-
Accessでフォームに自動入力し...
-
Accessのフォーム上のテキスト...
-
Microsoft365にAccessってあり...
-
Access VBA [リモートサーバー...
-
テーブルとクエリの相関図は表...
-
Accessのリンクテーブルのパス...
-
エクセルのマクロについて教え...
-
Access で半角スペースと全角ス...
-
accessの代わりになるもの
-
ACCESS2019でのエラーメッセージ
-
Access VBA を利用して、フォル...
-
Accessのスプレッドシートエク...
-
実行時エラー3131 FROM 句の構...
-
Access Error3061 パラメータが...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Access レポート印刷するときに...
-
Access Error3061 パラメータが...
-
Microsoft365にAccessってあり...
-
Accessのクエリで、replace関数...
-
Accessのリンクテーブルのパス...
-
Access VBA [リモートサーバー...
-
ACCESS VBA でのエラー解決の根...
-
accessデータを指定したExcel、...
-
Accessのスプレッドシートエク...
-
CSVファイルの「0落ち」にVBA
-
【Access】Dcount関数の複数条...
-
Accessのフォーム上のテキスト...
-
Access VBA を利用して、フォル...
-
実行時エラー3131 FROM 句の構...
-
Vba Userformを前面に出すについて
-
Accessでフォームに自動入力し...
-
Accessレポートのチェックボッ...
-
Accessのテキストボックスの入...
-
Access 複数条件検索の設定が上...
-
accessのフォームに設置したボ...
おすすめ情報