Excel vba Tips 表形式のデータをリスト形式に変換する

利用想定ケース：既存の票をpivotのデータとして使いたいとき
利用方法：表を選択した状態でマクロ実行

before

	愛媛	和歌山
2012	100	110
2013	150	160

after

2012	愛媛	100
2012	愛媛	110
2013	和歌山	150
2013	和歌山	160

Option Explicit

Sub table2list()
    Dim c As Range
    Dim cols() As String
    Dim i As Integer
    Dim labelYColN As Integer
    Dim labelXRowN As Integer
    Dim pointer As Integer
    
    ReDim cols(Selection.Columns.Count)
    i = 1
    labelYColN = Selection(1).Column
    labelXRowN = Selection(1).Row
    pointer = Selection(Selection.Count).Row + 2
    
    Dim labelY As String
    For Each c In Selection
        If labelXRowN = c.Row Then
            cols(i) = c.Value
            i = i + 1
        ElseIf labelYColN = c.Column Then
            labelY = c.Value
        Else
            ActiveSheet.Cells(pointer, labelYColN).Value = labelY
            ActiveSheet.Cells(pointer, labelYColN + 1).Value = cols(c.Column)
            ActiveSheet.Cells(pointer, labelYColN + 2).Value = c.Value
            pointer = pointer + 1
        End If
    Next

End Sub

Excel 目次

関数 Tips

Excel vba

Tips

• 表形式のデータをリスト形式に変換する

Apache POI sample code Excelのセルの値を読み取る

Apache POIはJavaからExcelを操作できる素敵ツール。
これ使い始めると、vbaとか怠くなる。

だって java の方が圧倒的にライブラリが充実してて便利なんだもん。
vbaだと List とか Setとかないのがめんどい。

対象version : poi-3.10-FINAL-20140208.jar

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class Book {
	public static void main(String[] args) throws FileNotFoundException, IOException{
		HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("xls/b042.xls"));
		wb.getNumberOfSheets();
		HSSFSheet sheet = wb.getSheet("B042");
		System.out.println(getCell(sheet, 10, 10));	
	}
	
	public static String getCell(HSSFSheet sh, int rowN, int colN){
		HSSFRow row = sh.getRow(rowN);
		HSSFCell cell = row.getCell(colN);
		return cell.getStringCellValue();
	}

}

reference

• official
• Java目次

「1-1」を「1-2」にする関数（数式）

よくExcelで、こんな表を作ります。

No	事柄
1-1	計画を立てる
1-2	実行する
1-3	結果を確認する

で、このときの
1-2以降を連番で振ってくれる数式

  =LEFT(A12, FIND("-",A2) ) & RIGHT(A2, LEN(A2)-FIND("-",A2) )+1