No.2ベストアンサー
- 回答日時:
こんばんは!
具体的な範囲が判らないので、やり方だけ・・・
↓の画像でA1~D10セル内データでG1セルに入力したデータの
「行番号」と「列番号」を表示するようにしてみました。
尚、範囲内に重複データはない!という前提です。
画像ではG2セルに
=SUMPRODUCT((A1:D10=G1)*ROW(A1:A10))
G3セルに
=SUMPRODUCT((A1:D10=G1)*COLUMN(A1:D1))
という数式を入れています。
※ 該当データがない場合は「0」が表示されます。m(_ _)m
ありがとうございます。
出来ました。機能としては十分です。しかし、これはどういう理屈で計算出来ているのでしょうか?sumproductは調べましたが分かりませんでした。
・A1:D10=G1は論理式の扱いなのか?どういう動作?
・ROW(A1:A10)は10個のセルの行番号を示すの?
・両者が掛け算されており、配列としては1個だと思いますが、sumproductの引数として1個の配列を使うのはナニ?
すみませんが、教えて下さい。
No.7
- 回答日時:
回答No.6の補足として別の角度から検討してみました。
貼付画像はExcel 2013で行ったものですがExcel 2003にも組み込まれた関数で処理しています。
行番号
H4=MOD(MIN(IF((A2:E11=H2),ROW(A2:A11)+COLUMN(A2:E2)*1000,"")),1000)
列番号
H5=INT(MIN(IF((A2:E11=H2),ROW(A2:A11)+COLUMN(A2:E2)*1000,""))/1000)
この2つの式は入れ子のIF関数で配列値を返す必要があるため数式バーへ入力後Ctrl+Shift+Enterで確定してください。
左上から右下へ検索して最初に見つかったセルが結果として返されます。
I4とI5の計算式はMIN関数をMAX関数に置き換えたもので、左上から右下へ検索して最後に見つかったセルが結果として返されます。
検証(別解)はSUMPRODUCT関数を使うことで式の確定時に通常のEnterキーのみで確定する扱いにしてみました。
行番号
L4=SUMPRODUCT(LARGE((INDEX(A2:E11,,SUMPRODUCT(LARGE((A2:E11=L2)*COLUMN(A2:E2),COUNTIF(A2:E11,L2))))=L2)*ROW(A2:A11),COUNTIF(INDEX(A2:E11,,SUMPRODUCT(LARGE((A2:E11=L2)*COLUMN(A2:E2),COUNTIF(A2:E11,L2)))),L2)))
L5=SUMPRODUCT(LARGE((A2:E11=L2)*COLUMN(A2:E2),COUNTIF(A2:E11,L2)))
この数式は論理が複雑なのでお勧めできません。
No.6
- 回答日時:
>平たく言うと何番目に見つかったか。
MATCH関数は指定範囲(1列または1行)の中から検索値の位置を検出します。
何番目かは指定できませんので検索値と同じ値のセルが複数のときは最初に見つかった位置になります。
>これにaddressをかけてなぜセル位置が分かるのでしょうか?
MATCH関数は複数列または複数行を対象にできませんので検出範囲は限定されています。
今回の質問で「指定範囲内で文字列を検索し」とのことですが複数行×複数行の場合は別の方法を使います。
また、「見つかったセルの行番号、列番号」は「A1」や「R1C1」のようにセルのアドレスとして検出したかったのではないのでしょうか?
条件が曖昧だったので勝手解釈の検証結果を提示しました。
尚、ADDRESS関数は行番号と列番号を指定することでセルの位置が返されます。
今回の検証では列は固定で行番号をMATCH関数で検出した結果を提示しました。
MATCH("第3項",B1:B14,0) → 4
ADDRESS(4,2) → $B$4 引数の2番目はB列(第2列)を意味します。
ADDRESS(4,2,4) → B4 引数の3番目は省略すると上記のようになり、$を除いた形式にするときは4を指定します。
複数列×複数行からの位置検出ではSUMPRODUCT関数を使えますが検索値と同じ値が複数のとき無指定で優先的に選択することができませんので条件の提示が必要です。
No.5
- 回答日時:
No.2です。
数式の意味が知りたいというコトですので・・・
画像の場合で説明しますと。
大前提としてデータ範囲内には重複データはない!というのは前回書いた通りです。
(万一同じデータが複数ある場合はとんでもない数値になります)
行方向の
>=SUMPRODUCT((A1:D10=G1)*ROW(A1:A10))
の意味
必ず範囲指定した行数と ROW(A1:A10) の行数は一致させる必要があります。
A1~D10セル内にG1と一致するデータがある場合(画像では範囲指定内の5行目)ですので、
5行目だけが「TRUE」=1 となり 他の行はすべて「FALSE」=0となります。
配列数式のSUMPRODUCT関数で、ROW(A1:A10) をかけていますので、
TRUEの5行目=1 × 行番号(A5)の結果が表示されます。
列に関しても同じ意味です。
※ 注意点 ※
万一 仮にデータ範囲がA1セルからではなく、C3セルから始まる範囲が前回のような表がある場合は
=SUMPRODUCT((C4:F13=G1)*ROW(A1:A10))
のように範囲指定した行数と同じ数だけ1行目(A列でなくても構いません)から同じ行数分だけ掛け算します。
この程度でよろしいでしょうかね?m(_ _)m
ありがとうございます。
これは今までに経験のない使い方です。
A1:D10=G1 まずこれは論理式で結果は配列となりFalse,F,F,F,True,F,F,F,F,F
ROW(A1:A10) これは行番号の配列1,2,3,4,5,6,7,8,9,10
両者を掛け算すべく*を使った時点でTrueとFalseはそれぞれ1と0に変わり、結果は配列となり、0,0,0,0,5(行番号),0,0,0,0,0
sumproductなので0+0+0+0+5+0+0+0+0+0=5が戻り値になる。
なかなか奥が深い使い方ですね。
No.1
- 回答日時:
>マクロでなく関数で出来るでしょうか?
ADDRESS関数を使うとセルの列記号と行番号を取得できます。
貼付画像はExcel 2013で検証した結果です。
MATCH関数を使って目的の値で行番号を取得し、ADDRESS関数に渡してセルの位置を取得しました。
=ADDRESS(MATCH("第3項",B1:B14,0),2,4)
ありがとうございます。
machを調べましたが、指定範囲にある検索文字列を探し「相対的な順番を返す」関数だったと思います。平たく言うと何番目に見つかったか。これにaddressをかけてなぜセル位置が分かるのでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【Excel】指定のセル内容を基に別シートのセルを検索して選択する【VBA】 1 2022/06/16 16:16
- Excel(エクセル) 指定文字列が該当するA列をアクティブセルにするには 3 2022/08/17 13:18
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
- Excel(エクセル) 日付で矢印マクロ 4 2023/07/25 16:47
- Excel(エクセル) Excelでの検索結果を含む行だけを表示させたい 5 2023/03/10 17:08
- Visual Basic(VBA) VBA 改行コードの取り方 1 2022/03/22 14:14
- Visual Basic(VBA) VBA 検索と入力 Excel ブック ぶぶぶ シート ししし 列V 検索対象の列です 最終行は、お 6 2023/05/17 01:40
- Excel(エクセル) 【VBA】A列の指定した値と同じ行にあるD列の値を順番にコピペするマクロについて 4 2023/02/01 18:16
- Visual Basic(VBA) vba 等間隔の列に対しての計算 6 2022/05/17 20:15
- Visual Basic(VBA) Excle VBA Findメソッドについて 3 2022/07/15 13:56
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
excelで検索値の入っているセル番地を取得するには?
Excel(エクセル)
-
EXCELで指定した文字列のアドレスを取得したい
Excel(エクセル)
-
値が入っている一番右のセル位置を返す方法
Excel(エクセル)
-
-
4
ある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?
Excel(エクセル)
-
5
エクセルで条件に一致したセルの隣のセルを取得したい
その他(Microsoft Office)
-
6
エクセルでアルファベットか数値の判定をしたいのですが
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
-1234 を (1234) と表記する由...
-
エクセルで現場ごとに機材の移...
-
他部署からもらう データで、 ...
-
Excel 1の位の数字で処理を分岐...
-
Excelのセルの色を変えた行(す...
-
エクセルの数式について教えて...
-
Excelでの記号と特殊文字につい...
-
とびとびの大量セルを選択した...
-
MACにおけるエクセルの入力トラ...
-
ある表にフィルターをかけて出...
-
Lookup関数
-
エクセルの数式バーのフォント...
-
再質問です。マクロの修正箇所...
-
エクセルで「ページレイアウト...
-
EXCELの散布図で日付が1900年に...
-
【マクロ】VLOOKUPにて参照元に...
-
exselの質問です
-
エクセルでファイルの最終更新...
-
Excel 大小比較演算子による「...
-
エクセルの問題です。絶対値の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報