EXCELで、例えば 0,7,0,0,16,0,3,1 というような列の並びがあったとして、これをワークシト関数だけで、別の行に 7,16,3,1 という列の並びに変換する(値が 0 のセルを無視して詰める)

にはどういう式をたてればいいのでしょう。
一発変換は無理なら道筋のアウトラインだけで結構です。よろしく

回答の条件
  • 1人2回まで
  • 登録:2006/07/19 12:47:18
  • 終了:2006/07/19 15:27:02

ベストアンサー

id:t-ueno No.3

t-ueno回答回数113ベストアンサー獲得回数32006/07/19 14:58:18

ポイント100pt

その数列が1行のみならば、以下のように関数だけで可能です。

(その数列の行がA11にあるものとして書いています)


その数列行の1行上(A10の行)に「=IF(A11<>0,1,"")」

→0でないセルの上に1を立てます


さらに1行上に「=IF(A11<>0,SUM($A10:A10),"")」

→1が立っているセルの上だけに1、2、3、4と連番を打ちます

 あとでlookupするので、それ以外のセルには空白が入るようにします


0を飛ばして詰め込みたい行(A13の行とします)の上の行(A12)に

1、2、3、4と順番に数字を入れます(ここは数式やisblankやcountifを

使って必要以上に数値が入らないようにしてもいいですね)


最後に詰め込みたい行(A13の行)に、以下のようにhlookupを入れます

「=HLOOKUP(A12,A9:H11,3)」

(これもisblank等を使って不要セルにエラーが出ないようにするとキレイ)


って感じでいかがでしょうか。

id:kubira711

SUMを使って1を集計し、この結果の値に連番の意味を持たせるとうい発想が出てこなかったので、

ありがとう御座いました。私が求めていた回答そのものです。

2006/07/19 15:23:29

その他の回答(2件)

id:gong1971 No.1

gong1971回答回数443ベストアンサー獲得回数682006/07/19 13:28:50

ポイント35pt

一発で求めるのは、ほぼ不可能かと思います。

仮計算の行を1行使用する方法でご案内します。


  1. 元の数値の上に1行挿入します。(元の数値A2:H2、挿入した行A1:H1とします。)
  2. A1に =COUNTIF($A$2:A2,"<>0") を入力します。
  3. A1のセルをB1:H1にコピーします。
  4. A3に =HLOOKUP(COLUMN(),$A$1:$H$2,2,0) を入力します。
  5. A3のセルをB3:H3にコピーし、#N/Aと表示されたセルを消去します。

※数式中セル範囲の H はデータ範囲に応じて変更してください。


以上、不明な点などありましたら、お知らせ下さい。


なお、数値は8個まで等、ある程度条件が限定されていれば、

一発で求める事も可能です。その他、条件によって別の方法を

ご案内する事が可能かもしれません。別途お知らせ下さい。

id:kubira711

質問の意味が不明快であったかもしれません。

スタチックに記述された式のみで解決したかったもんですから。

2006/07/19 15:25:08
id:tomozo117 No.2

tomozo117回答回数12ベストアンサー獲得回数02006/07/19 14:13:10

ポイント35pt

1.0のセルを空白セルに置き換えます。

2.8ヶのセル範囲を選択し、空白セルをジャンプ(Ctrl-G)させます。

http://www2.odn.ne.jp/excel/waza/edit.html#SEC32

id:kubira711

質問の意味が不明快であったかもしれません。

スタチックに記述された式のみで解決したかったもんですから。

質問の意味が不明快であったかもしれません。

スタチックに記述された式のみで解決したかったもんですから。

2006/07/19 15:25:18
id:t-ueno No.3

t-ueno回答回数113ベストアンサー獲得回数32006/07/19 14:58:18ここでベストアンサー

ポイント100pt

その数列が1行のみならば、以下のように関数だけで可能です。

(その数列の行がA11にあるものとして書いています)


その数列行の1行上(A10の行)に「=IF(A11<>0,1,"")」

→0でないセルの上に1を立てます


さらに1行上に「=IF(A11<>0,SUM($A10:A10),"")」

→1が立っているセルの上だけに1、2、3、4と連番を打ちます

 あとでlookupするので、それ以外のセルには空白が入るようにします


0を飛ばして詰め込みたい行(A13の行とします)の上の行(A12)に

1、2、3、4と順番に数字を入れます(ここは数式やisblankやcountifを

使って必要以上に数値が入らないようにしてもいいですね)


最後に詰め込みたい行(A13の行)に、以下のようにhlookupを入れます

「=HLOOKUP(A12,A9:H11,3)」

(これもisblank等を使って不要セルにエラーが出ないようにするとキレイ)


って感じでいかがでしょうか。

id:kubira711

SUMを使って1を集計し、この結果の値に連番の意味を持たせるとうい発想が出てこなかったので、

ありがとう御座いました。私が求めていた回答そのものです。

2006/07/19 15:23:29
  • id:gong1971
    t-uenoさんの回答を更にシンプルにしたのが私の回答だったのですが...。
    (t-uenoさんの場合は仮計算行が3行、私の場合は仮計算行が1行。)

    スタティックな式という視点でも違いは無いと思うのですが...。
    とは言え、うまく伝わらなかったのは私の書き方のせいなので、
    よい勉強になりました。

    参考までに、t-uenoさんの「=IF(A11<>0,1,"")」と
    「=IF(A11<>0,SUM($A10:A10),"")」を「=COUNTIF($A$2:A2,"<>0")」で求め、
    「1、2、3、4と順番に数字を入れます」と「=HLOOKUP(A12,A9:H11,3)」を
    「=HLOOKUP(COLUMN(),$A$1:$H$2,2,0)」で求めており、基本的な考え方は
    同じになります。よかったら試して頂けると幸いです...。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません