スポンサーリンク

全列をワンタッチで簡単にソートできるOpenOffice表計算シートのテンプレート (オートフィルタ風の並べ替えをOOo Calcで簡単に実現)


表計算シート上の全ての列を,ワンタッチでソート(並べ替え)できる。



Excelの「オートフィルタ」と同じ並び替え機能を,
無料のOpenOfficeでも実現している。


このテンプレートのダウンロード:

http://www.name-of-this-site.org/codi...

※OpenOfficeのCalcで開くこと。

※必ず,ローカルに保存してから開くこと。
 読み取り専用だとソートも実行できないから。

※マクロを有効にするか聞かれたら,有効化を選ぶこと。

↑このファイルで,各列の▲とか▼を押してみよう。

一瞬でソートされる。


なお列の見出しを追加すると,ソートボタンが自動的に現れる。

このテンプレートの解説

もともとOpenOfficeでは,Excelと違って
オートフィルタに「ソート機能」がない。

また,並び替えダイアログをツールバーから呼び出すこともしづらい。


つまり,本来のOpenOfficeでは各列をかんたんに並び替える方法がないのだ。なんと不便。

faq/4/538 - OpenOffice.org Q&A
http://oooug.jp/faq/index.php?faq%2F4...

  • 要望なのですがオートフィルタのプルダウンに 昇順で並べ替え、降順で並べ替えの機能を追加して頂きたいです。


一目で分かるOpenOffice 2.x の使い方 - Calc 第5回 セル・データを思い通りにソート:ITpro
http://itpro.nikkeibp.co.jp/article/C...

  • Calcで,セル・データを並べ替えるには2つの方法がある。
    • ツール・バーの「昇順で並べ替え」や「降順で並べ替え」のボタンを使う方法と,
    • 「データ」メニューにある「並べ替え」を使う方法だ。
  • ただし,Microsoft OfficeのExcelと違って,ツール・バーによる操作は,機能が限定的である。
    • ほとんどの場合は,メニューの「並べ替え」機能を使う必要がある。


そこを解決しようというのが今回の試み。


OpenOfficeにも,VBAと同じようなマクロ機能がある。(OpenOffice Basic

マクロを使えば,列のソートは簡単に実現できる。


だから,シート内にソート処理のマクロをあらかじめ仕込んでおけばよい。

シート上ではそのマクロを呼び出すだけ。

OpenOffice.calcのBasicマクロで,簡単に列をソートするサンプルコード
http://computer-technology.hateblo.jp/entry/20150524/p2

  • OOo Calcで,シート内の任意の列をソートするマクロのサンプルコード。


問題は,このマクロをどうやって各列に埋め込むか? だ。

全ての列に,個別にボタンを設置して,
各ボタンごとにそれぞれマクロを埋め込む必要がある。

この設置作業を手動でやると大変だ。


そこで,もっと簡単な手として,
「セルのHYPERLINK関数からマクロを呼び出す」
という手段がある。

これなら,セルをフィルするだけでボタンがコピーできる。

OpenOffice.org Calcのセル内で,HYPERLINK関数だけでマクロを実行する方法 (表計算シート上にボタン塔を設置せずに,OpenOffice Basicを簡単に実行する)
http://computer-technology.hateblo.jp/entry/20150522/p1

  • OOo Calcで,セル内のHYPERLINK関数だけでマクロを実行できる。

全コード

以上のことを踏まえて,ブック内部で使われているマクロを全掲載。

REM  *****  BASIC  *****


' 列をASCでソート
Sub PresetSortAsc(str_from_cell)
	Call PresetSort str_from_cell, True
End Sub


' 列をDESCでソート
Sub PresetSortDesc(str_from_cell)
	Call PresetSort str_from_cell, False
End Sub


' 列をソート
Sub PresetSort( s, asc_flag )

	' セルから渡される列番号を取得
	Const token ="&col=" 
	pos = instr(s, token) + len(token)
	arg_val = mid( s, pos )
	abs_column_x = val(arg_val) - 1
	column_index = abs_column_x - 1
		' http://computer-technology.hateblo.jp/entry/20150522/p1

	' ソート準備
	Dim sort_info(1) As New com.sun.star.beans.PropertyValue
	Dim sort_fields(0) As New com.sun.star.util.SortField
	
	' ソート範囲
	Set oCellRange = getMySortRange()

	' 何番目の列をどういう優先順位でソートするか
 	sort_fields(0).Field = column_index ' 列番号,0始まり
 	sort_fields(0).SortAscending = asc_flag ' ASCかどうか
	sort_info(0).Name = "SortFields"
 	sort_info(0).Value = sort_fields()

 	' range内に列のヘッダを含むかどうか
 	sort_info(1).Name = "ContainsHeader"
	sort_info(1).Value = False

	' 範囲を選択してソート実行
	ThisComponent.getCurrentController().select( oCellRange )
	oCellRange.sort( sort_info() )

	' ソートに利用したカラムを選択状態にする
	Set select_range = ThisComponent.getCurrentController(). _
		getActiveSheet(). _
		getCellRangeByPosition(abs_column_x, 1, abs_column_x, 1, 0)
	ThisComponent.getCurrentController().select(select_range)

End Sub


' ソート範囲のrange
Function getMySortRange
	sheet_index = 0

	' ソート範囲の左上
	start_y = 4
	start_x = 1

	' 2列目の最終行
	last_y = EndXlDownInSheet( sheet_index, 1 )
	' 2行目の最終列
	last_x = EndXlToRightInSheet( sheet_index, 1 )

	' 四隅を指定
	oCellRange = ThisComponent. _
		Sheets. _
		getCellRangeByPosition(start_x, start_y, last_x, last_y, sheet_index)
	
	' 返却
	getMySortRange = oCellRange
End Function


' テスト実行用
Sub hoge
End Sub



' ----------- Excel VBAとの互換機能のコード ----------- 



' 引用元:
' http://computer-technology.hateblo.jp/entry/20150524/p1


' あるシート内で,ある列の中に最後にデータが存在する行番号を0始まりで返す。
' Excel VBAの End(xlDown) のパクリ。
' 引数のシート番号と列番号は0始まりで指定すること。
Function EndXlDownInSheet( sheet_index, column_index )
	
	' シート
	oSheet = ThisComponent.Sheets( sheet_index )

	' シート内の特定の列
	oColumn = oSheet.getColumns().getByIndex(column_index)
	
	' セル検索用の定数
	magic_number = 31
	
	' 列内から,「値の入っている一続きのセル範囲」を複数取得
	oRanges = oColumn.queryContentCells(magic_number)

	' ひとつながりの領域がいくつあるか
	nRangeCount = oRanges.getCount()
	
	' 一つながりの領域が1つもなかったら
	If nRangeCount = 0 Then
		' どこにもデータはない
		nBottomRow = -1
	Else
		' 最後に現れる一つながりの領域
		oRange = oRanges.getByIndex(nRangeCount - 1)
		
		' その領域内で,データが入っている最終行の番号 (0始まり)
		nBottomRow = oRange.getRangeAddress().EndRow
	End If
	
	' 返却値
	EndXlDownInSheet = nBottomRow

End Function


' あるシート内で,ある行の中に最後にデータが存在する列番号を0始まりで返す。
' Excel VBAの End(xlToRight) のパクリ。
' 引数のシート番号と行番号は0始まりで指定すること。
Function EndXlToRightInSheet( sheet_index, row_index )
	
	' シート
	oSheet = ThisComponent.Sheets( sheet_index )

	' シート内の特定の行
	oRow = oSheet.getRows().getByIndex(row_index)
	
	' セル検索用の定数
	magic_number = 31
	
	' 行内から,「値の入っている一続きのセル範囲」を複数取得
	oRanges = oRow.queryContentCells(magic_number)

	' ひとつながりの領域がいくつあるか
	nRangeCount = oRanges.getCount()
	
	' 一つながりの領域が1つもなかったら
	If nRangeCount = 0 Then
		' どこにもデータはない
		nLastColumn = -1
	Else
		' 最後に現れる一つながりの領域
		oRange = oRanges.getByIndex(nRangeCount - 1)
		
		' その領域内で,データが入っている最終列の番号 (0始まり)
		nLastColumn = oRange.getRangeAddress().EndColumn
	End If
	
	' 返却値
	EndXlToRightInSheet = nLastColumn

End Function

シート上で,ソート実行ボタンを設置したいセルには,下記のように関数を書く。

=IF(LEN(B$2)>0;HYPERLINK("vnd.sun.star.script:Standard.Module1.PresetSortAsc?language=Basic&location=document&COL="&COLUMN();"▲ 0-9");"")

=IF(LEN(B$2)>0;HYPERLINK("vnd.sun.star.script:Standard.Module1.PresetSortDesc?language=Basic&location=document&COL="&COLUMN();"▼ 9-0");"")

これでセル内のハイパーリンクからマクロを呼び出せる。

列の見出しが未記入の場合は,ソートボタンも表示されないようにしてある。

結論

このようにOpenOfficeは無料といえど,工夫次第ですごく便利になる。


OOoやLibreOfficeの活用のカギは,Basicマクロとの効率的な連携だ。

マクロを使いこなせば,OpenOfficeの無料のありがたみがわかる。

今回のサンプルは非常に良い例だと思う。


なおOpenOfficeマクロは,Webや書籍などの情報源で学ぶことができる。

Basicというだけあって,VBAと文法がほぼ同じ。

OpenOffice.org Calcで,VBAに似たマクロ「OpenOffice Basic」の入門サイト集。シート上の定型処理を自動化する「OOo Basicマクロ」を初歩から学ぶ
http://computer-technology.hateblo.jp/entry/20150521/p1

  • Excelの無料版ともいえる「Open Office.org Calc」の,マクロ機能に入門するためのサイト。
  • Excel VBAとほぼ同じコードが,フリーソフトであるCalc上でも動作する。 名前は「OpenOffice Basic」。
  • このOpenOfficeマクロを,まったく知識ゼロの状態から学習できるサイトを集めた。

関連する記事:

WSHバッチで,OpenOffice.org Calcを自動操作する方法 (表計算のブックを,COM経由で新規作成・読み書き・保存)
http://language-and-engineering.hatenablog.jp/entry/20141227/OOoCalcByWSHJScript


ドキュメント作成を楽にするための,Excel VBA 頻出8パターン
http://language-and-engineering.hatenablog.jp/entry/20090401/p1


「実行可能ドキュメント」が満たすべき性質 − テスト自動化ツール「Excelenium」で使われている技術や手法
http://language-and-engineering.hatenablog.jp/entry/20101112/p1