excelに関する質問です
| A | B |
1|001|斉藤|
-+--+------
2|001|渡辺|
-+--+------
3|001|田中|
-+--+------
4|001|斉藤|
-+--+------
5|002|飯田|
-+--+------
6|002|矢田|
-+--+------
7|002|飯田|
-+--+------
8|001|渡辺|
-+--+------
9|001|田中|
10| 001 | 斉藤
11| . | 渡辺
12| . | 田中
A1:B9にデータが入っています。
A10に番号「001」を入れるとB10:B12に
その番号のデータを検索してきて入るように
関数を利用したいのですが、上手くいきません
(A10に番号「002」を入れると飯田、矢田のデータが表示される)
MATCH関数とindirect関数、index関数などを使い、検索結果が重複して表示されるようには出来たのですが
やり方を教えて下さい、お願いします。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
似たようなテクニックを業務で多用しています。
補助セルをデータの両サイドに作ります。
タイトル行も作ります。元データと抽出行の間にも、空白行を入れておきます。イメージは下のようになります。
|A| B | C | D
--+-+---+----+------
1|0 No 氏名 No+氏名
2|1 001斉藤 001斉藤
3|2 001渡辺 001渡辺
4|3 001田中 001田中
5|3 001斉藤 001斉藤
6|3 002飯田 002飯田
7|3 002矢田 002矢田
8|3 002飯田 002飯田
9|3 001渡辺 001渡辺
10|3 001田中 001田中
11|
12|1 001斉藤
13|2 渡辺
14|3 田中
15|4 #N/A
各セル範囲(の左上のセル)には以下の数式が入ります。数式を記さないところは単なる文字や数字です。
【D2:D10】=$B2&$C2
【A2:A10】=A1+($B2=$B$12)*ISNA(VLOOKUP($D2,$D$1:$D1,1,FALSE))*1
【C12:C15】=VLOOKUP($A12,$A$2:$C$10,3,FALSE)
まず、【D2:D10】にNoと氏名を合体した文字列を作ります。
【A2:A10】が肝です。条件に合致するデータを数え上げています。
($B2=$B$12)…NoがB12のセル(上の例では001)と等しい時にTrue(=1)
ISNA(VLOOKUP($D2,$D$1:$D1,1,FALSE))…Noと氏名が同じ組合せが1つ上の行までに既出でない時にTrue
(例えば、5行目の001 斉藤さんは2行目で既出なのでFalse)
その2つを掛け算した部分(=式のA1+を抜かした部分)は、上記2つの条件に当てはまる時(=抽出対象である時)に1になります。A1+をつけることにより、単なる0と1ではなく、その累計を取っています。
ですので、1行目のタイトル(特に【A1】の0)は飾りではなく、必要不可欠です。
最後に、【C12:C15】で条件にあったデータを取ってきます。単なるVLOOKUPなので、データがなければ#N/Aが表示されます。
ちなみに、ISNA関数を取り除くと、udagawaさんがお作りになった関数と同じ結果になると思います。
No.4
- 回答日時:
#3です。
#もう、ユーザー定義関数のほうが楽だと思います。
以下は、配列数式より、マシかなっていうレベルのものです。あまり、計算スピードは速くありません。
ひさびさに、Dictionary オブジェクトを使ってみました。
'標準モジュール設定
'------------------------------------------------------------
Function FindValues(findtxt As String, myArea As Range, indx As Long) As String
'Dictionary を使った、重複を省く検索,
' findtxt 検索文字列, myArea 2列の範囲, indx は、検索後のデータの序数
Dim objDic As Object
Dim dicAry As Variant
Dim outAry As Variant
Dim i As Long
Dim j As Long
Dim c As Variant
Set objDic = CreateObject("Scripting.Dictionary")
i = 1
For Each c In myArea.Columns(1).Cells
On Error Resume Next
objDic.Add c.Value & "," & c.Offset(, 1).Value, i
If Err.Number = 0 Then
i = i + 1
Else
Err.Clear
End If
Next
dicAry = objDic.keys
outAry = Filter(dicAry, findtxt & ",")
FindValues = Mid(outAry(indx - 1), InStr(outAry(indx - 1), ",") + 1)
End Function
'------------------------------------------------------------
ワークシートには以下のように、一般の関数と同じように入れます。
B10:
フィルダウン・コピー
=FindValues($A$10,$A$1:$B$9,ROW(A1))
No.3
- 回答日時:
こんばんは。
重複データがあるわけですよね。
まず、最初に、それを排除するということから、検索をするというのは、かなり負担が大きいような気がします。最初に、重複を、[フィルタオプションの設定]コマンドで排除というわけにはいかないのでしょうか?
>MATCH関数とindirect関数、index関数などを使い、検索結果が重複して表示されるようには出来たのですが
それなら、そのまま当てはめられるはずです。
そうでないなら、補助セルを使わないと、たぶん、ネストの制限に引っかかるような気がします。
補助セル
D1:(検索ヒット数)
=SUM((IF(MATCH($A$1:$A$9&$B$1:$B$9,$A$1:$A$9&$B$1:$B$9,0)=ROW($A$1:$A$9),$A$1:$A$9,"")=$A$10)*1)
要『配列の確定』
B10:
=IF($D$1<ROW(A1),"",INDEX($B$1:$B$9,SMALL((IF(MATCH($A$1:$A$9&$B$1:$B$9,$A$1:$A$9&$B$1:$B$9,0)=ROW($A$1:$A$9),$A$1:$A$9,"")=$A$10)*ROW($A$1:$A$9),ROWS($A$1:$A$9)-$D$1+ROW(A1)),1))
要『配列の確定』
※『配列の確定』
一旦、数式のところで、F2を押して、再度、SHIFT キーとCTRL キーを押しながら、ENTER を押すと、式が実体化して、値が出てきます。
このレベルになると、もう、ユーザー定義関数のほうが楽だと思います。
No.2
- 回答日時:
》 関数を利用したいのですが、…
私なら、こういうときのためにある[フィルタオプションの設定]を使うけど、面倒な関数に固執する理由は何ですか?
》 MATCH関数とindirect関数、index関数などを使い、
》 検索結果が重複して表示されるようには出来たので
》 すが…
ほほ~。どのように出来たのですか?出来るだけそれを利用したもので考えてみたいです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) ユーザーフォーム「frm_基本❶」を立ち上げると新規で入力する行数を右下のNoとして表示しています。 1 2023/03/16 19:02
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- Excel(エクセル) エクセル表作成について 5 2023/03/12 13:25
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
- Excel(エクセル) ExcelのVLOOKUP関数 7 2022/08/23 06:46
- その他(データベース) 20万行あるデータを動かしたい 2 2023/06/13 15:21
- C言語・C++・C# c言語の問題です 2 2023/07/21 10:51
- Excel(エクセル) Excel ピボットテーブルの表示方法を変更したいのですが、、、(合計値を下部に) 3 2023/06/18 21:10
- Excel(エクセル) エクセルでエラーを無視して一番左側のセルの値を返したい 2 2023/07/27 13:06
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】スペースがいくつ入っ...
-
西暦や和暦の表示をyyyymmdd表...
-
【Microsoft Office Excel Comp...
-
Excelはなんで先頭の0を消すん...
-
Excelのセルを飛ばして入力する
-
別シートからの文字を変更
-
エクセルの行の抽出について質...
-
Excelのオートフィル
-
Excel 2019 のピボットテーブル...
-
スプレッドシート クエリ関数 1...
-
excelの不要な行の削除ができな...
-
Excel初心者です。 詳しい方、...
-
【Excel】セル内の時間帯が特定...
-
Excel初心者です。 詳しい方、...
-
EXACT関数とIF関数の組み合わせ...
-
Excelのグラフ軸について
-
スマートな関数を教えて下さい。
-
Excelで全角を半角にしたいので...
-
【マクロ】エクセルにかいてあ...
-
Excel:一部のフォントでセルの...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報