Excel 値が入っている左隣の文字を表示させるには、、

Excel初心者です。
左の表から右のような表を作りたいのですが、
そのために、以下の動きをすればいいと思ってます。
① C列に数字がはいっていたら、H列にその数字を詰めてもってきたい。
=IFERROR(INDEX(C:C,SMALL(IF(C:C<>"",ROW(C:C)),ROW())),"")
これは、↑の数式でうまくいきました。
② C列に数字がはいっていたら、その左にあるA・B列の文章をF・G列に表示したい。
これの数式がわからないのですが、ぜひわかる方いましたら教えていただけないでしょうか？
八方塞がりで、困ってます。

そもそも、もっとシンプルでいい方法があるよって方いましたら、
教えていただけると助かります…！

質問者からの補足コメント

• 素早い返事ありがとうございます！
  
  ご指摘のように、
  =IFERROR(INDEX(C:C,SMALL(IF(C:C<>"",ROW(C:C)),ROW())),"")
  だと、できますが、任意の位置にできませんでした、、
  任意の位置が３列目からだと、元表の３列目の値が出てくるようです。
  （補足参照）
  
  これはfujillinさんが教えてくださった式も同様でした。
  ですが、式挿入したところ、きれいにでて感動しました。可能性はないと思っていたので、
  本当にありがとうございます。
  
  差し出がましい質問なのですが、
  さすがに、これ以上は自分で表の位置を動かさないと無理ですよね、

  
  No.1の回答に寄せられた補足コメントです。 補足日時：2020/07/13 16:14

No.1 ベストアンサー 回答者： fujillin 回答日時： 2020/07/13 15:09

こんにちは

ご提示のＨ列の値だけを手掛かりに、元の表から値を検索しようとしても、Ｃ列の値がユニークであることが保証されているわけではないので、結局、元の検索方法から検索し直さないとダメなようです。
（ご提示の例で、5や6が複数あるので、どれを採用すれば良いのか単純には判断できない）
また、ご提示の関数式も、そのままでは例示の結果にはならないと思われます。

＞↑の数式でうまくいきました。
であるのなら、INDEXの対象部分だけ可変にして、C～A列を参照するようにしておいて、INDEX値（＝SMALL以下）は変わらないように絶対参照にすれば、フィルコピー可能な式にできるはずです。


ほぼ同様の計算ロジックですが、配列数式として扱わなくても良い関数式の例を以下に示しておきます。（式は少し長くなりますが）
ご提示の例の、F3セルに
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($C$1:$C$99)/($C$1:$C$99<>""),ROW(A1))),"")
の式を入れて、右方向、下方向にフィルコピーでいかがでしょうか？

この回答へのお礼

何度も丁寧に対応いただき、回答をいただきありがとうございました！
Excel精進します！

お礼日時：2020/07/14 14:29

No.10 回答者： banzaiA 回答日時： 2020/07/14 10:47

#2です。

そうですか、2016ですね。
そのバージョンでは無理かと思います。365でないと。
任意のセルに、
=filter(a1:c13,c1:c13<>"","")
を記述するだけで、一気にお望みの表が作成できるのですが。

この回答へのお礼

そうなんですね！
365では、シンプルな関数でできるというのには驚きです。
私のPCにもはやく実装されればいいのですが・・・

貴重な参考となる回答ありがとうございました！

お礼日時：2020/07/14 13:54

No.9 回答者： banzaiA 回答日時： 2020/07/14 08:36

#2です。

＞何度やっても、うまくいかず、、
Excelのバージョンはどうなっていますか？
提示したのは、Excelバージョン　365でのことです。

この回答へのお礼

Excel2016ってかいてあります。

お礼日時：2020/07/14 10:20

No.8 回答者： mike_g 回答日時： 2020/07/13 21:05

[No.7]補遺、

提示式中の末尾の ROW(1:1) は ROW(A1) に變更願ひます。
今考へれば其の方が自然だし、女性のスカートの丈と式は出來るだけ短い方が好きなので(*^_^*)

この回答へのお礼

お返事ありがとうございます

mike_gさんの関数で出來ました！
＜＞””を＞0であらわしていて、なるほど、、と思いました。（違ったらすみません）

梅雨が明けたら町行く人々の装いも涼やかになるので、待ち遠しいですね＾＾

回答ありがとうございました！

お礼日時：2020/07/14 14:27

No.7 回答者： mike_g 回答日時： 2020/07/13 20:55

同じシートの３列以上の空き地の任意のセルに次式を入力して、其のセルを右２列にオートフイルした當該３セルを下方にズズーッとオートフィル

=IFERROR(INDEX(A$1:A$13,SMALL(IF($C$1:$C$13>0,ROW(A$1:A$13)),ROW(1:1))),"")
【御斷はり】上式は必ず配列數式として入力の事

No.6 回答者： tom04 回答日時： 2020/07/13 17:55

こんにちは！

横からお邪魔します。
補足の数式を拝見すると配列数式になっていますね。

そのまま配列数式にしてみました。
尚、どこのセルに入力しても大丈夫です。

表示したいセルに
=IFERROR(INDEX(B$1:B$1000,SMALL(IF($C$1:$C$1000<>"",ROW($B$1:$B$1000)),ROW(A1))),"")

前述のように配列数式なので、Ctrl＋Shift＋Enterで確定！（←必須★）し
フィルハンドルで列・行方向にコピーしてみてください。

※　C列の1～3行目に項目などがあるとそれも表示されてしまいます。
画像通り4行目以降にデータがあり、それ以降を範囲指定したい場合は
=IFERROR(INDEX(B$4:B$1000,SMALL(IF($C$4:$C$1000<>"",ROW($B$4:$B$1000)-3),ROW(A1))),"")

（配列数式の操作必要です）
にしてみてください。

※　余談ですが、数式内の「ROW(A1)」の部分は「SMALL関数」の「順位」の部分にあたりますので
必ず「1」からにする必要があります。

※　配列数式はPCに大変負担をかけ、範囲が広すぎると極端に計算速度が落ちます。
すなわち、列全体などは範囲指定しないことをおススメします。

上記数式は1000行程度の範囲にしています。

範囲は実情に合わせて広げてみてください。m(_ _)m

この回答へのお礼

こんにちは
お返事ありがとうございます

tom04さんの関数でもできました！

＞余談ですが、数式内の「ROW(A1)」の部分は「SMALL関数」の「順位」の部分にあたりますので
必ず「1」からにする必要があります。
＞配列数式はPCに大変負担をかけ、範囲が広すぎると極端に計算速度が落ちます。
すなわち、列全体などは範囲指定しないことをおススメします。

特にROWについては、1からにする必要があるとのご指摘でいろいろ理解が進んだので、
非常に参考になりました！
ありがとうございます！

お礼日時：2020/07/14 14:10

No.5 回答者： fujillin 回答日時： 2020/07/13 17:37

No3です。

＞参照セルを変更したのですが、ずれてしまうため、質問しました。
変更するからずれるのではないでしょうか？
補足の添付図を見る限り、元の表はＡ～Ｃ列にあるので1～99行までの範囲にある限りは式を変えなくても機能します。
（実際の行位置はご質問の時と異なるようですが、検索して表示する仕組みなので行が変わっても問題にならない）

抽出して表示なさりたい範囲が、Ｋ～Ｍ列で（行数が読み取れませんが）仮に3行目からとするなら、Ｋ3セルにNo1の回答のままの関数を入力すれば良いはずです。
（5行目スタートであるなら、Ｋ5セルにそのままを入力）

もしも、Ａ～Ｃ列にその他の（対象外の）データ等があって、検索対象範囲をキチンと指定しないと余分なデータまで混ざってしまうとかであれば、式中のセル範囲を修正する必要がありますが…

この回答へのお礼

お返事ありがとうございます！

何度もやり直しつつ、理解を進めたところ、
本番資料でもfujillinさんの関数できれいにできました。

＞変更するからずれるのではないでしょうか？

その通りでした。
以下私がやってしまったことです。
本番資料（本番資料はA４から始まる）に合わせて元表（最初の画像にあるA1から始まる表）をずらしたら、関数がずれた。ので、ROWで指定している位置を変えればいいのではと色々変えたが、さらにずれる結果に。
→同じシート上で元表をずらさずに、別シートでA4から始まる元表を作ってから、
fujillinさんの関数をそのまま入れたらうまくいきました。

原因はなんだかよくわからないのですが、ROWが1からスタートするというのに、
原因があるのでは、、とうっすら思っています。

文章だけではなにしたかったのか伝わらないと思いますが、
とりあえずできました！

何度もお返事いただきありがとうございました！！！

お礼日時：2020/07/14 14:07

No.4 回答者： bonaron 回答日時： 2020/07/13 17:22

VBA でよければ

Application.Intersect(Columns(3).SpecialCells(xlCellTypeConstants, 23).EntireRow, Columns("A:C")).Copy
Range("F3").PasteSpecial
Application.CutCopyMode = False

問題のシートをアクティブにして実行。

この回答へのお礼

すみません、
ドがつく初心者なので、そこまでは、、、やり方がわからず、、、
回答していただき、ありがとうございます！

お礼日時：2020/07/14 08:26

No.3 回答者： fujillin 回答日時： 2020/07/13 16:35

No1です。

＞これはfujillinさんが教えてくださった式も同様でした。
多分、違うはずです。

No1の式は表示したい範囲の、左上となるセル位置に回答で提示した式をいれれば良いように作成してあります。
（元の提示の図で言えば、F3セルに入れる式です）

補足に添付されている図では（数式バーがボケてよく読めませんが）、No1の回答式を改変して使っているように見えます。
改変などする前に、まずそのままで使ってみて、意味をきちんと理解できたなら、ご自由に変更なされば宜しいかと思います。

＞さすがに、これ以上は自分で表の位置を動かさないと無理ですよね、
何のことをおっしゃっているのか意味がわかりません。
元の表の位置が変わるのなら、式の中の参照セルを変更すれば良いでしょう。
結果を表示したい範囲を変えたいのであれば、上記のように、その左上セルに回答の式を入れればすむはずです。

この回答へのお礼

あほな質問してるのに、お返事ありがとうございます！

＞改変などする前に、まずそのままで使ってみて、意味をきちんと理解できたなら、ご自由に変更なされば宜しいかと思います。

fujillinさんのおっしゃる通り、F3セルに入れると完璧に綺麗に入ることは確認済です。
そのため、本番用の表で試してみたところ、ずれたため、先ほどの補足で本番と同じ表の位置に改変して、質問しました。表の位置をずらしたところ、本番用と同じずれ方をしたので、添付して質問しました。

＞結果を表示したい範囲を変えたいのであれば、上記のように、その左上セルに回答の式を入れればすむはずです。

もちろんおっしゃる通り、参照セルを変更したのですが、ずれてしまうため、質問しました。

＞意味をきちんと理解できたなら
もう一度勉強しなおします。

丁寧にありがとうございます。

お礼日時：2020/07/13 17:04

No.2 回答者： banzaiA 回答日時： 2020/07/13 16:17

Microsoft365 です。

=filter(a1:c13,c1:c13<>"","")
でEnter　でできます。

この回答へのお礼

すみません、
何度やっても、うまくいかず、、、
Windows10なので、できると思うのですが、、
回答ありがとうございました。

お礼日時：2020/07/14 08:23