エクセルに関してです。


sheet1に、

AAAAAAA,A,B,C,D,E,1,2,3,4,5
BBBBBBB,A,B,C,D,E,1,2,3,4,5
CCCCCCC,A,B,C,D,E,1,2,3,4,5

 ・
 ・
があったとします。

・AAAAAAAは、任意の文字列
・A,B,C,D,Eは、任意の文字列だが、必ず、5つのセルに分かれている(空欄の場合もある)
・1,2,3,4,5は、任意の文字列だが、必ず、5つのセルに分かれている(空欄の場合もある)

これを

sheet2に

AAAAAAA-A,1
AAAAAAA-A,2
AAAAAAA-A,3
AAAAAAA-A,4
AAAAAAA-A,5
AAAAAAA-B,1
AAAAAAA-B,2
AAAAAAA-B,3
AAAAAAA-B,4
AAAAAAA-B,5
AAAAAAA-C,1
AAAAAAA-C,2
AAAAAAA-C,3
AAAAAAA-C,4
AAAAAAA-C,5
AAAAAAA-D,1
AAAAAAA-D,2
AAAAAAA-D,3
AAAAAAA-D,4
AAAAAAA-D,5
AAAAAAA-E,1
AAAAAAA-E,2
AAAAAAA-E,3
AAAAAAA-E,4
AAAAAAA-E,5
BBBBBBB-A,1
BBBBBBB-A,2
BBBBBBB-A,3
BBBBBBB-A,4
BBBBBBB-A,5
BBBBBBB-B,1
BBBBBBB-B,2
 ・
 ・
 ・
のように、連番の後にハイフンをつけた後にA,B,C,D,Eを。
そして、次のセルに1,2,3,4,5を入れる関数をお教えください。
一番最初に作ってくださった方にはイルカ賞を進呈いたします。
よろしくお願いいたします。

回答の条件
  • URL必須
  • 1人2回まで
  • 登録:2009/04/10 15:24:40
  • 終了:2009/04/10 17:40:08

ベストアンサー

id:kn1967 No.1

kn1967回答回数2915ベストアンサー獲得回数3012009/04/10 16:26:22

ポイント27pt

Sheet2のA1に

=INDIRECT("Sheet1!A" & INT((ROW()-1)/25)+1) & "-" &
INDEX(INDIRECT("Sheet1!B" & INT((ROW()-1)/25)+1 & ":F" & INT((ROW()-1)/25)+1), MOD(ROW()-1,5)+1)

長いので2行に書いてますが1行です。


Sheet2のB1に

=INDEX(INDIRECT("Sheet1!G" & INT((ROW()-1)/25)+1 & ":K" & INT((ROW()-1)/25)+1), MOD(ROW()-1,5)+1)

あとは下方向コピー


http://q.hatena.ne.jp/ダミー

その他の回答(2件)

id:kn1967 No.1

kn1967回答回数2915ベストアンサー獲得回数3012009/04/10 16:26:22ここでベストアンサー

ポイント27pt

Sheet2のA1に

=INDIRECT("Sheet1!A" & INT((ROW()-1)/25)+1) & "-" &
INDEX(INDIRECT("Sheet1!B" & INT((ROW()-1)/25)+1 & ":F" & INT((ROW()-1)/25)+1), MOD(ROW()-1,5)+1)

長いので2行に書いてますが1行です。


Sheet2のB1に

=INDEX(INDIRECT("Sheet1!G" & INT((ROW()-1)/25)+1 & ":K" & INT((ROW()-1)/25)+1), MOD(ROW()-1,5)+1)

あとは下方向コピー


http://q.hatena.ne.jp/ダミー

id:kaiton No.2

kaiton回答回数260ベストアンサー獲得回数342009/04/10 16:50:17

ポイント27pt

回答がかぶるかもしれませんが、できたので..


Sheet1も2もA1セルからデータがあるものとします。

Sheet2のA1に

=INDIRECT("Sheet1!A"&INT((ROW()+24)/25))&"-"&OFFSET(Sheet1!$B$1,INT(((ROW()+24)/25)-1),MOD(INT(((ROW()+24)/5)),5))&","&OFFSET(Sheet1!$G$1,INT(((ROW()+24)/25)-1),MOD((ROW()+4),5))

これを下にコピーします。


空欄の場合の処理が明記されていなかったので、関係なく5行ずつ作成するようにしています。

空欄の場合は「AAAAA-,」のようなデータになるので、「-,」でオートフィルターして削除した方が速いかもしれません。


INDORECT関数、OFFSET関数は 以下の説明を参照下さい。

http://www.relief.jp/itnote/archives/001697.php

http://officetanaka.net/excel/function/function/offset.htm

id:nobnob3 No.3

考え中回答回数321ベストアンサー獲得回数292009/04/10 16:59:23

ポイント26pt

もっと簡単な方法があると思いますが、力技で作ってみました。

配列数式など?

試してみて下さい

=CONCATENATE(INDIRECT("Sheet1!A"&TEXT(INT((ROW()+24.5)/25),"0")),"-",INDIRECT("Sheet1!"&CHOOSE(IF(MOD(ROW(),25)=0,5,INT((MOD(ROW(),25)+4.5)/5)),"B","C","D","E","F")&TEXT(INT((ROW()+24.5)/25),"0")),",",INDIRECT("Sheet1!"&CHOOSE(IF(MOD(ROW(),5)=0,5,MOD(ROW(),5)),"G","H","I","J","K")&TEXT(INT((ROW()+24.5)/25),"0")))

もしもっとスマートなのが出来たらまた書きます。

http://blog.livedoor.jp/gha10771/

  • id:nobnob3
    やはり達人の書く関数は美しいですね。勉強になります。

    まず、私の回答ですが、勘違いをして一つのセルに結果を出力していました。
    達人のに比べるとぼろぼろですが、以下が正しかったです。
    A1には
    =CONCATENATE(INDIRECT("Sheet1!A"&TEXT(INT((ROW()+24.5)/25),"0")),"-",INDIRECT("Sheet1!"&CHOOSE(IF(MOD(ROW(),25)=0,5,INT((MOD(ROW(),25)+4.5)/5)),"B","C","D","E","F")&TEXT(INT((ROW()+24.5)/25),"0")))

    B1には
    =INDIRECT("Sheet1!"&CHOOSE(IF(MOD(ROW(),5)=0,5,MOD(ROW(),5)),"G","H","I","J","K")&TEXT(INT((ROW()+24.5)/25),"0"))
    でした。

    kn1967さんのは短く美しいのですが、質問者の意図に合わせるためには、多分下記が正しいと思います。

    =INDIRECT("Sheet1!A"&INT((ROW()-1)/25)+1)&"-"&
    INDEX(INDIRECT("Sheet1!B"&INT((ROW()-1)/25)+1&":F"&INT((ROW()-1)/25)+1),(MOD(ROW(),25)-1)/5+1)

    kaitonさんのoffset関数も応用が広そうです。勉強になりました。
  • id:kaiton
    回答したものの補足 ,以降後半がB列なら
    A1=INDIRECT("Sheet1!A"&INT((ROW()+24)/25))&"-"&OFFSET(Sheet1!$B$1,INT(((ROW()+24)/25)-1),MOD(INT(((ROW()+24)/5)),5))
    B1=OFFSET(Sheet1!$G$1,INT(((ROW()+24)/25)-1),MOD((ROW()+4),5))
    これを下にコピーです。
  • id:kaiton
    nobnob3 様
    たしかに、kn1967さんの回答に補足されている部分
    最後の (MOD(ROW(),25)-1)/5+1 ですが、問題なく結果が出ていますが
    1.2 , 1.4 とかになっているので
    INT関数をいれて INT((MOD(ROW(),25)-1)/5)+1 が無難ではないかと思いますが
    どうでしょうか?
  • id:kn1967
    >(MOD(ROW(),25)-1)/5+1
    25行で1組と捉えるのではなく、5列で1組と捉えて
      MOD(ROW()-1,5)+1
    にて計算していました。訂正感謝します。

    INT((ROW()-1)/25)+1 に関しても
    INT((ROW()+24)/25)) のほうが的確ですね。
    前回質問に対する回答との対比を考えたつもりでしたが
    計算式としての美しさ(効率の良さ)はダメダメでした・・・。
    http://q.hatena.ne.jp/1239332794
  • id:martytaka
    nobnob3 様

    =CONCATENATE(INDIRECT("Sheet1!A"&TEXT(INT((ROW()+24.5)/25),"0")),"-",INDIRECT("Sheet1!"&CHOOSE(IF(MOD(ROW(),25)=0,5,INT((MOD(ROW(),25)+4.5)/5)),"B","C","D","E","F")&TEXT(INT((ROW()+24.5)/25),"0")))

    に、
    ・A,B,C,D,Eは、任意の文字列だが、必ず、5つのセルに分かれている(空欄の場合もある)
    と書きましたが、空欄の場合は
    ・ハイフンはつけない
    ってするには、どうしたらよいでしょうか?

  • id:kaiton
    #2で回答したものです。
    A2=INDIRECT("Sheet1!A"&INT((ROW()+23)/25)+1)&TEXT(OFFSET(Sheet1!$B$2,INT(((ROW()+23)/25)-1),MOD(INT(((ROW()+23)/5)),5)),"-#;;;-@")
    と別質問へ回答しました。
    http://q.hatena.ne.jp/1239702971
    B2=OFFSET(Sheet1!$G$2,INT(((ROW()+23)/25)-1),MOD((ROW()+3),5))
    C2=OFFSET(Sheet1!$B$2,INT(((ROW()+23)/25)-1),MOD(INT(((ROW()+23)/5)),5))&""

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

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

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

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