質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.44%

Q&A

1回答

735閲覧

Excelで元表からか、同じIDの行(複数)を別な表への転記

koisann

総合スコア10

0グッド

0クリップ

投稿2023/03/04 06:40

編集2023/03/04 06:43

イメージ説明リンク内容### 実現したいこと

Excel上で表を作成(元表)、その中にIDカラムが有ります。表の中から同じIDの行(複数)データを別な表に転記したい

###ここに実現したいことを箇条書きで書いてください。
転記先の表に元表カラムを設け、指定ID値の元表カラムを数式で作成。この値で元表のデータを転記する。

前提

転記先カラムの数式は同一、転記先のデータ部の式も同一にしたい。

転記先データ部の数式は
=IF($S3<>"",INDEX(B:B,$S3),"")
$S 転記したいID値
B:B 転記したいデータの先頭カラム

転送先カラム部の数式
=IF(COUNTIF($C:$C,$T$2)>=ROW(S1),LARGE(INDEX(($C$1:$C$494=$T$2)*ROW($C$1:$C$494),,),COUNTIF($C:$C,$T$2)-ROW(S1)+1),"")

解かりずらいので改行を追加。*以下、私なりのコメントを追加
=IF(COUNTIF($C:$C,$T$2)>=ROW(S1) *IFの条件マッチした数で判断
,LARGE( *IF 真の場合で行番号を算出
INDEX( **これが解りません? 指定した範囲の行列で交差した値を取り出すと理解
($C$1:$C$494=$T$2)*ROW($C$1:$C$494) *範囲?
,,) *範囲全体?
,COUNTIF($C:$C,$T$2)-ROW(S1)+1  *LARGEの位置
)
,"" *IF 偽 ブランクを記載
)

####ここに質問の内容を詳しく書いてください。

LARGE()で元表のカラムを算出。
INDEX()はLARGE()の範囲ですが理解できません。
($C$1:$C$494=$T$2) の=は何を意味しますか?範囲($C$1:$C$494)を制約している?
($C$1:$C$494=$T$2)*ROW($C$1:$C$494) の*は何を意味しますか?=ROW($C$1:$C$494)は1です。
COUNTIF($C:$C,$T$2)-ROW(S1)+1 はLARGE()の位置。元表のIDの数--です。

発生している問題・エラーメッセージ

エラーではありません。数式の動作の詳細を訪ねています。

該当のソースコード

Excelの数式

ソースコード
=IF(COUNTIF($C:$C,$T$2)>=ROW(S1),LARGE(INDEX(($C$1:$C$494=$T$2)*ROW($C$1:$C$494),,),COUNTIF($C:$C,$T$2)-ROW(S1)+1),"")

イメージ説明

試したこと

特に記述なし

補足情報(FW/ツールのバージョンなど)

画像2枚を添付しました。上記Excelは自作版の転記処理。元表のCがIDでID1を転記しています。行番号部、データ部の数式が見えています。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

meg_

2023/03/04 07:45

> エラーではありません。数式の動作の詳細を訪ねています。 質問は何でしょうか?現在の数式は作成者の思った通りに動作しているのでしょうか?何か課題があるのでしょうか?
koisann

2023/03/05 12:35

この記述でなせ選択したセルが選ばれるのでしょうか?どんな内部処理をしているのか知りたいのです。関数の中にセルの配列を記述すると具体的にどんな動きをするのか知りたいのです。こう記述すれば出来るだけでは理解できたとは言えないと思っています。
meg_

2023/03/05 12:44

> どんな内部処理をしているのか知りたいのです VBAで用意されている関数の内部処理を知るのは困難かと思います。(ソースコードは公開されていないかと思いますので)
koisann

2023/03/05 12:45

=IF( COUNTIF($C:$C,$T$2)>=ROW(S1) *IFの第一引数 ,LARGE( *IFが真の処理 INDEX(($C$1:$C$494=$T$2)*ROW($C$1:$C$494),,) ★?LARGEの範囲 これは何を意味しているか ,COUNTIF($C:$C,$T$2)-ROW(S1)+1 ★LARGEのインデックス 配列$C:$Cの$T$2の数から-1づつした値 ) ,"" *IFが偽の処理 ブランク ) 上記のLARGEで範囲を指定していますが、これは具体的にどんな動きをするのでしょうか。
koisann

2023/03/07 12:20

VBAは使用していません。INDEX(($C$1:$C$494=$T$2)*ROW($C$1:$C$494),,)の行列の指定が無い場合(この場合は,,)はセル値ではなく範囲全体になるnetで見つけました。(これなら、なんとなく理解出来そう)この時範囲の内容は($S$2の値(1ですね))*その行番号の集合との理解で良いのでしょうか? 新たに集合が作られたように見えます。この値をセルに移すことは出来るのでしょうか?(具体的な値が知りたい) INDEXの戻り値がセル値でない場合が有ることを知りませんでした。
meg_

2023/03/07 16:01

すみません。VBAではなくワークシートの関数でしたね。
退会済みユーザー

退会済みユーザー

2023/03/25 03:00 編集

「*その行番号の集合との理解で良いのでしょうか?」この「*」が「掛け算」を意味しているのなら、おおむねその理解でよいです。私の回答を参照してください。
guest

回答1

0

配列・配列数式

まず、配列/配列数式について簡単に説明します。

配列とは、簡単にいえば、複数のセルを1つの範囲としてまとめたものです。
そして配列数式とは、配列を作る/扱うための数式表現、または数式そのものを指します。

肌感覚で配列数式をつかむため、一番簡単な配列を作ってみましょう。
たとえばA1セルに

excel

1=D3:F3

と入力すると、
下図のように、D3からF3までのセル内容がそのまま表示されます。
イメージ説明
この「=D3:F3」が「配列数式」であり、この数式が指しているものが「配列」です。

配列数式の計算

配列数式を使うと何がメリットなのか?
それは、複数の範囲のセルを一括して計算することができる、という点です。

さきほどのA1セルに入力した数式を下記のように変えてみます(末尾に & "!"を追加)

excel

1=D3:F3 & "!"

そうすると下図のように、3つのセル全部に「!」が追加されました。
イメージ説明
もし配列を使わなかった場合、最初は、

A1セルに=D3&"!"、B1セルに=E3&"!"、C1セルに=F3&"!"... と入力

の必要があり、その後、末尾に「!」をつけたいと思ったら

A1セルを=D3&"!"、B1セルを=E3&"!"、C1セルを=F3&"!"... に変える

というように1個1個の数式を修正していかなければなりません。

配列を使うことで、複数のセル範囲に含まれる各セルに対して同じ処理を一括して行うことができることが分かります。

そして、配列に対して行える処理としては、上記のような文字列の連結だけではなく、四則演算比較も含まれます。

text

1<まとめ> 2・配列を使うと、複数セルを一括して処理できる。 3・可能な処理は、文字列の連結や、四則演算、比較など。

数式の解析

配列について簡単に触れたところで、質問の数式についてみていきましょう

EXCEL

1=IF(COUNTIF($C:$C,$T$2)>=ROW(S1),LARGE(INDEX(($C$1:$C$494=$T$2)*ROW($C$1:$C$494),,),COUNTIF($C:$C,$T$2)-ROW(S1)+1),"")

$ がついていると見にくいので。いったん$を消したベースで説明します。($は連続コピペ時に考慮すればよいため)

EXCEL

1=IF(COUNTIF(C:C,T2)>=ROW(S1),LARGE(INDEX((C1:C494=T2)*ROW(C1:C494),,),COUNTIF(C:C,T2)-ROW(S1)+1),"")

1.INDEX((C1:C494=T2)*ROW(C1:C494),,),

1-1. (C1:C494=T2)*ROW(C1:C494)

1-1-1. (C1:C494=T2)

この数式ですが、
配列(C1:C494)= セル値(T2)
となっています。

つまり配列について「比較」を行っているということですね

よってこの数式は「左の配列の中の各セルが、右のセル値に等しい場合はTRUE、異なる場合はFALSEである配列を返す」、という意味になります。

(この「配列を返す」というところが重要です)

たとえば適当なセルに「=(C1:C494=T2)」を記入すると、
下の図のように、

  • 配列「C1:C494」内の各セルのうち、T2(=1)と等しいセルは TRUE, 異なるセルは FALSE として並べた配列

が返ってきていることが分かります。

イメージ説明

1-1-2. ROW(C1:C494)

ROW(C1:C494)は、行番号の配列を返します。(下図)
イメージ説明

1-1-3. (C1:C494=T2)*ROW(C1:C494)

(C1:C494=T2)*ROW(C1:C494) という数式は、
「配列どうしを掛け算して、その結果を新たな配列として返す」という処理を行っています。(1.で申し上げた通り、配列に対しては四則演算処理が可能)

つまり
TRUE,FALSE が並んだ配列(1-1-1)× 行番号の配列 (1-1-2)
を各要素で行っていることになります。

ここで、EXCELの計算では、TRUEは「1」、FALSEは「0」 と解釈されます。

したがって、「(C1:C494=T2)*ROW(C1:C494)」は下の図のように

  • 「C1:C494」の範囲で、値がT2(=1)であるセルの行番号(ただし1ではないセルはゼロ)の配列

を表すことになります。

イメージ説明

 

1-3. INDEX(~,,)

INDEX 関数は、INDEX(配列,行番号,列番号) とすると、配列のうち、指定した行番号・列番号のセルのセル参照を返します。
ただしここでは、行番号、列番号が省略されていますので、配列そのものを返すことになります。

実は 質問のユースケースの場合、最新のExcelであればINDEXで囲まなくても正常に動作します
なのでここではINDEXの意味についてあまり考える必要はないと思います。

INDEXが使われている理由は

  • 過去古いバージョンのExcelだと、INDEXがないと動作しなかったから。
  • 複数の表を参照する等のカスタマイズを行えるようにするため。

のいずれかと考えられます。

2. LARGE(<1>, COUNTIF($C:$C,$T$2)-ROW(S1)+1)

 
LARGE 関数:
たとえば LARGE(X, Y) とすると、下記の値を返します。

  • 配列X を大きい順に並べ変えた配列のうち、Y 番目の値

一方、「=COUNTIF($C:$C,T$$2)-ROW(S1)+1」 を適当なセルに縦にコピペしていくと、
下の図のように、「C列のうちT2の値の個数=3で始まり、1ずつ小さくなっていく数」になっています。

イメージ説明

よって、これをLARGE関数と組み合わせた「 LARGE(<1>, COUNTIF($C:$C,$T$2)-ROW(S1)+1)」という数式(※1)を縦にコピペ(※2)すると

  • 1行目: 「LARGE(<1>, COUNTIF($C:$C,$T$2)-ROW(S1)+1)」

  →<1>の配列のうち、大きい順から3番目の数 =3

  • 2行目: 「LARGE(<1>, COUNTIF($C:$C,$T$2)-ROW(S2)+1)」

  →<1>の配列のうち、大きい順から2番目の数 =9

  • 3行目: 「LARGE(<1>, COUNTIF($C:$C,$T$2)-ROW(S3)+1)」

  →<1>の配列のうち、大きい順から1番目の数 =10

が抽出されることになります。
(※1:「<1>」というのは、上で説明した「0,0,3,0,0,0,0,0,9,10,.....」という配列のことです)
(※2:この数式自体は配列を返す数式ではなく、単純な計算結果しか返さないので、縦に数式をコピペする必要あり)

4番目以降は、0からマイナス値になっていますが、数式の前半の「COUNTIF($C:$C,$T$2)>=ROW(S1)」の条件に当てはまらないので、最終的にはIF文により 空欄("")が表示されることになります。


質問者さんの記載に沿って答えると下記のようになります。

text

1=IF(COUNTIF($C:$C,$T$2)>=ROW(S1) *IFの条件マッチした数で判断→【OK】 2,LARGE( *IF 真の場合で行番号を算出 →【指定した第1引数の配列を、大きい順に並べたとき。第2引数に指定した順位のセルを返す関数】 3INDEX( **これが解りません? →【実は不要】 4($C$1:$C$494=$T$2)*ROW($C$1:$C$494) *範囲?→【C列のうち、T2セルと同じ値となっている行の行番号の配列。ただしT2セルと異なる値のセルはゼロ】 5,,) *範囲全体? →【OK】 6,COUNTIF($C:$C,$T$2)-ROW(S1)+1  *LARGEの位置 →【OK】 7) 8,"" *IF 偽 ブランクを記載 →【OK】 9)

INDEX()はLARGE()の範囲ですが理解できません。

→上記のように少なくとも最新のExcelではINDEXで囲まなくても動作します。おそらく過去の互換性のためか、カスタマイズのためでしょう。

($C$1:$C$494=$T$2) の=は何を意味しますか?範囲($C$1:$C$494)を制約している?

→配列の各要素が $T$2 セルの値と「等しいかどうか」を比較しています。最終的には比較した結果(TRUE/FALSE)の配列を返しています。

($C$1:$C$494=$T$2)*ROW($C$1:$C$494) の*は何を意味しますか?=ROW($C$1:$C$494)は1です。

→配列どうしの掛け算を意味しています。
左の配列の各要素と、右の配列の(対応する)各要素の掛け算の結果が、新たな配列として返ってきます。
=ROW($C$1:$C$494)は1ではなく、行番号の配列になります。


・動作確認環境
Microsoft Excel バージョン2301(Microsoft 365)
OS:Windows 10 Home バージョン 22H2

・回答を作成するにあたって参考にした資料
INDEX 関数 - Microsoft サポート
配列数式のガイドラインと例 - Microsoft サポート
Excelまたは配列を返す関数 - Microsoft サポート

投稿2023/03/05 04:13

編集2023/03/12 01:07
退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.44%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問