人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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
のようにです。

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

●質問者: martytaka
●カテゴリ:ビジネス・経営 コンピュータ
✍キーワード:24 A1 AA- MOD イルカ賞
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

1 ● kaiton
●53ポイント ベストアンサー

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

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


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

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

◎質問者からの返答

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

とても助かりました。

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


2 ● Mook
●35ポイント

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


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

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

位置調整が可能です。

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/

◎質問者からの返答

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

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

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ