入力された数値とその左側の数値（文字）で表の作成

質問

http://okwave.jp/qa/q8214979.html（「入力された数値とその右側（左側でした）の数値（文字）での表作成」）で質問した者です。その際は大変お世話になりました。有り難うございました。その後、その回答に対して新たに質問があり「補足」で質問させて頂きました。しかし、トピックを新たに立てた方が回答が寄せられると思い、トピックを立てさせて頂きました。ご理解を頂けばと思います、

　質問内容は、下の「表A」から、数値が入力され、かつ「0」よりおおきな数値だけを選んで（この場合「B列）、その数値の左側あるのセルの文字（この場合「A列」。数値の場合もあります）と一緒に移動させて、「表B」を作成することができる関数はどのようにつくれば良いのでしょうか、改めてご教示をお願いします。宜しくお願いします。

回答No.4

MarcoRossiItaly · ベストアンサー

関数というか、前回と同様、Ctrl+Shift+Enter の操作が不要である数式を載せてみます。今回も A 列は、数値と文字列のどちらを記入してあっても平気です。

D2　=iferror(index($A:$B,small(index(row($B$2:$B$3000)+9^9*(countif(indirect("b"&row($B$2:$B$3000)),"")+countif(indirect("b"&row($B$2:$B$3000)),"<=0")),),rows(D$2:d2)),columns($D2:d2)),"")

※ D2 セルをコピーして D2:E10 のセル範囲に貼り付け

前回との違いは、行数を適当に 3,000 行に増やしてみたのと、COUNTIF 関数を 1 つ追加したということです。

まあオートフィルタとかフィルタオプションのほうが簡単で実用的だという状況は、今回も同じです。

あと結果的にデータが表示されなかった D7:E10 のセルの枠線を自動的に非表示にすることも可能です。条件付き書式という機能を使います。

今回は、ご回答を寄せてくださる皆さんに反応してくださると、回答者としてはたいへん助かります。ベストアンサーとかには、ほとんど関心がないのですが。私の場合はたまたま今回のご質問を発見したので、まだ良かったです。

お礼

完璧でした。本当に有り難うございました。実際にはA、Bの２列ではなく、A、B、Cの３列で抽出しました（行数は50列程度）。確かにオートフィルタとかフィルタオプションを利用した方が簡単ですが、関数で行いたかったので質問させて頂きました。またの機会にお世話になるかもしれませんが、その際は宜しくお願いたします。

回答No.3

tom04

こんにちは！
一例です。

↓の画像のようにC列を作業用の列とします。
C2セルに
=IF(B2>0,ROW(),"")
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

E2セルに
=IFERROR(INDEX(A:A,SMALL($C:$C,ROW(A1))),"")
という数式を入れ、列・行方向にオートフィルでコピー！
これで画像のような感じになります。

※　作業列を使わずに一気にやりたい場合は
表示したいセルに
=IFERROR(INDEX(A$2:A$1000,SMALL(IF($B$2:$B$1000>0,ROW($A$1:$A$999)),ROW(A1))),"")

これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合は上記数式をコピー　→　表示したいセルを選択
→　数式バー内に貼り付け　→　そのまま（編集可能のまま）Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これを列・行方向にオートフィルでコピー！でも同じ結果となります。

上記数式は1000行目まで対応できる数式にしていますが、配列数式はPCにかなりの負担を掛けますので
データ量が極端に多い場合は最初の作業列を使う方法をおススメします。m（_ _）m

回答No.2

noname#204879

何を血迷っていたか、[No.1]は聊か冗長過ぎました。
次のように訂正します。
G2: =IF(B2,ROW(),"")
H2: =SMALL(G:G,ROW(A1))
D2: =IF(ISERROR($H2),"",INDEX(A:A,$H2))

回答No.1

noname#204879

G2: =IF(B2,ROW()+B2/10000,"")
H2: =SMALL(G:G,ROW(A1))
I2: =MATCH(H2,G:G,0)
D2: =IF(ISERROR($I2),"",INDEX(A:A,$I2))