http://q.hatena.ne.jp/1239344679

で、質問を、したところ、

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"))

という回答を頂きました。

この関数に、一手間加えて頂きたいのです。

●ハイフンをつけるという仕様でしたが、
「A,B,C,D,Eが空欄の場合には、ハイフンはつけない」
ってするには、どうしたらよいでしょうか?

●また、sheet1もsheet2も、A2から始まる場合は、どのようにしたら
よいでしょうか?

●尚且つsheet2に、A,B,C,D,Eを単独で入れる
つまり、
AAAAAAA-A,1,A
AAAAAAA-A,2,A
AAAAAAA-A,3,A
AAAAAAA-A,4,A
AAAAAAA-A,5,A
AAAAAAA-B,1,B
AAAAAAA-B,2,B
AAAAAAA-B,3,B
AAAAAAA-B,4,B
AAAAAAA-B,5,B
のようにです。

必ずイルカ賞を進呈させて頂きます。
よろおしくお願いいたします。

回答の条件
  • URL必須
  • 1人2回まで
  • 登録:2009/04/14 18:56:13
  • 終了:2009/04/15 10:13:06

ベストアンサー

id:kaiton No.1

kaiton回答回数260ベストアンサー獲得回数342009/04/14 19:35:21

ポイント53pt

前回より、空欄の場合の判断を追加しました。

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)),"-#;;;-@")

途中の=OFFSET(Sheet1!$B$2,INT(((ROW()+23)/25)-1),MOD(INT(((ROW()+23)/5)),5)) でA,B,C,D,Eの部分を表示させています。

これだけだと、空欄は0で表示(計算)されるので、その性質を利用して、TEXT関数の表示形式で0は非表示、

正の数又は文字の場合、頭に - を表示させました。

負の数だと--1のようになるので、A,B,C,D,Eの部分は負の数はないもの(文字か正の数字を想定)としています。


これは、前回に回答し、コメントで補足した内容と同じです。

B2=OFFSET(Sheet1!$G$2,INT(((ROW()+23)/25)-1),MOD((ROW()+3),5))

C列もA,B,C,D,Eが空欄なら表示しないようにしました。

後ろの &"" がそのための式で、これがなければ 0 で表示されます

C2=OFFSET(Sheet1!$B$2,INT(((ROW()+23)/25)-1),MOD(INT(((ROW()+23)/5)),5))&""

なお、ROW関数は、そのセルが何行目かを表す関数です。

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

INT(((ROW()+23)/25)-1)の式だけで試すとわかりやすいと思います。


表示形式は↓の説明を見てください。

http://www.eurus.dti.ne.jp/~yoneyama/Excel/user_set.htm


うまくいかないなら、コメントで補足しますので、コメントを書けるようにしてください。

あわてて終了せずに、じっくり検証してください。

id:martytaka

本当に、誠に、ありがとうございました。

とても助かりました。

本当にありがとうございました。

2009/04/15 10:12:00

その他の回答(1件)

id:kaiton No.1

kaiton回答回数260ベストアンサー獲得回数342009/04/14 19:35:21ここでベストアンサー

ポイント53pt

前回より、空欄の場合の判断を追加しました。

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)),"-#;;;-@")

途中の=OFFSET(Sheet1!$B$2,INT(((ROW()+23)/25)-1),MOD(INT(((ROW()+23)/5)),5)) でA,B,C,D,Eの部分を表示させています。

これだけだと、空欄は0で表示(計算)されるので、その性質を利用して、TEXT関数の表示形式で0は非表示、

正の数又は文字の場合、頭に - を表示させました。

負の数だと--1のようになるので、A,B,C,D,Eの部分は負の数はないもの(文字か正の数字を想定)としています。


これは、前回に回答し、コメントで補足した内容と同じです。

B2=OFFSET(Sheet1!$G$2,INT(((ROW()+23)/25)-1),MOD((ROW()+3),5))

C列もA,B,C,D,Eが空欄なら表示しないようにしました。

後ろの &"" がそのための式で、これがなければ 0 で表示されます

C2=OFFSET(Sheet1!$B$2,INT(((ROW()+23)/25)-1),MOD(INT(((ROW()+23)/5)),5))&""

なお、ROW関数は、そのセルが何行目かを表す関数です。

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

INT(((ROW()+23)/25)-1)の式だけで試すとわかりやすいと思います。


表示形式は↓の説明を見てください。

http://www.eurus.dti.ne.jp/~yoneyama/Excel/user_set.htm


うまくいかないなら、コメントで補足しますので、コメントを書けるようにしてください。

あわてて終了せずに、じっくり検証してください。

id:martytaka

本当に、誠に、ありがとうございました。

とても助かりました。

本当にありがとうございました。

2009/04/15 10:12:00
id:Mook No.2

Mook回答回数1312ベストアンサー獲得回数3912009/04/14 22:39:14

ポイント35pt

特に「関数で」という制限がなかったのでマクロで作成してみてはどうでしょうか。


関数は、今回のように開始位置を変更したいというときにオフセットの調整が煩わしい

ですが、マクロでしたらセルにデータとして展開できますので、作成後に行の挿入等で

位置調整が可能です。

Sub makeList()
    Dim dstWB As Workbook
    Dim dstWS As Worksheet

    Set dstWB = Workbooks.Add()
    Set dstWS = dstWB.Worksheets(1)
    
    Dim lastRow As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    Dim i As Long, j As Long, k As Long
    Dim data As String
    For i = 1 To lastRow
        For j = 0 To 4
            If Cells(i, 2 + j).Value <> "" Then
                data = Cells(i, "A").Value & "-" & Cells(i, 2 + j).Value
            Else
                data = Cells(i, "A").Value
            End If
            For k = 0 To 4
                dstWS.Cells((i - 1) * 25 + j * 5 + k + 1, "A").Value = data
                dstWS.Cells((i - 1) * 25 + j * 5 + k + 1, "B").Value = Cells(i, 7 + j).Value
            Next
        Next
    Next
End Sub

データのあるシートのタブを右クリックし、「コードの表示」で表示されるウィンドウに上記コードを貼り、

EXCEL に戻った後、ALT+F8で、Sheet1!makeList のように表示されているものを実行するば新規にデータが

作成されます。


ブックにマクロを残したくない場合、上記の表示の手順で貼り付けたものを削除すれば元の状態に戻ります。

今回はそれを懸念して、新規ファイルで作成するようにしましたが(作成されたファイルはマクロを含まない)

同じブックの2番目のシートにデータを作成したい場合、コード中の

    Set dstWB = Workbooks.Add()
    Set dstWS = dstWB.Worksheets(1)

の代わりに

    Set dstWS = ThisWorkbook.Worksheets(2)

とすればそうなります。


関数での解法をお望みでしたら、読み飛ばしてください(ポイントは不要です)。

http://excelvba.pc-users.net/

id:martytaka

関数でが目的でしたのでご了承ください。

ありがとうございました。

2009/04/15 10:12:47

コメントはまだありません

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

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

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

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