—
– Created by: Takaaki Naganoya
– Created on: 2024/12/02
—
– Copyright © 2024 Piyomaru Software, All Rights Reserved
—
use AppleScript version "2.4"
use scripting additions
use framework "Foundation"
tell application "Numbers"
tell front document
tell active sheet
try
set theTable to first table whose class of selection range is range
on error
return "" –何も選択されてなかった場合
end try
–セルを選択中の表を処理する
tell theTable
set headR to header row count
set headC to header column count
set rowC to count every row
set colC to count every column
repeat with y from (headR + 1) to (rowC)
–1行分（ヘッダーカラムをのぞく）のデータを1D Listで取得
set tmpRangeStr to numAdrToColumnEncode(headC + 1) of AddressEncoder & (y as string) & ":" & numAdrToColumnEncode(colC) of AddressEncoder & (y as string)
set tValList to value of cells of range tmpRangeStr
–1D Listから値が連続している区間を求める
set sameBlock to detectSameValueRepeatationBlockIn1DArray(tValList) of detectRepeatation
–連続区間でループ
repeat with i in sameBlock
copy i to {startX, endX} –データ内の項目Indexであることに留意
–重複値のセルをクリアする（初出セルの値は残す）
repeat with ii from (startX + 1) to endX
set value of cell (ii + headC) of row y to ""
end repeat
–重複していたセルをマージする
set mRangeStr to numAdrToColumnEncode(headC + startX) of AddressEncoder & (y as string) & ":" & numAdrToColumnEncode(headC + endX) of AddressEncoder & (y as string)
set mRange to range mRangeStr
merge mRange
end repeat
end repeat
end tell
end tell
end tell
end tell
script detectRepeatation
property parent : AppleScript
use scripting additions
use framework "Foundation"
–巨大なデータの処理に向いていないかも？
on detectSameValueRepeatationBlockIn1DArray(aList)
set dList to returnDuplicatesOnly(aList) of me
–> {"年間仕様", "YP仕様", "月間仕様"}
set anArray to current application’s NSMutableArray’s arrayWithArray:aList
anArray’s addObject:""
set resList to {}
repeat with i in dList
set j to contents of i
set anIndex to (anArray’s indexOfObject:j)
repeat with ii from (anIndex + 1) to ((length of aList))
set jj to (anArray’s objectAtIndex:ii) as string
if jj is not equal to j then
set the end of resList to {anIndex + 1, ii}
exit repeat
end if
end repeat
end repeat
resList
–> {{4, 6}, {7, 9}, {10, 17}}
end detectSameValueRepeatationBlockIn1DArray
on returnDuplicatesOnly(aList)
set countedSet to current application’s NSCountedSet’s alloc()’s initWithArray:aList
set simpleSet to current application’s NSSet’s setWithArray:aList
countedSet’s minusSet:simpleSet
return countedSet’s allObjects() as list
end returnDuplicatesOnly
end script
script AddressEncoder
property parent : AppleScript
–10進数数値をExcel 2004/2008的カラム表現にエンコードするサブルーチン（エンコード範囲：1～1351）
on numAdrToColumnEncode(origNum)
if origNum > 1351 then
error "エラー：Numbersのカラム表現（A1形式）への変換ルーチンにおいて、想定範囲外（1351以上）のパラメータが指定されました"
end if
set upperDigitEncTable to {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "A"}
set lowerDigitEncTable to {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "A"}
set oNum to origNum
set nTh to 26
set stringLength to 4
–数字が１桁の場合の対応
if origNum < 27 then
set aRes to (item origNum of upperDigitEncTable) as string
return aRes
end if
if origNum > 702 then
–3桁になる場合
set upupNum to oNum div 676 –整数除算–上の上の桁
set oNum to oNum – (upupNum * 676)
set upNum to oNum div 26 –整数除算–上の桁
set lowNum to oNum mod 26 – 1 –余剰計算–下の桁
–つじつま合わせ処理 【強引】
if lowNum = -1 then
set upNum to upNum – 1
set lowNum to 25
end if
set upupChar to (item upupNum of upperDigitEncTable) as string
set upChar to (item upNum of upperDigitEncTable) as string
set lowChar to (item (lowNum + 1) of lowerDigitEncTable) as string
set resText to upupChar & upChar & lowChar
else
–2桁の場合
set upNum to oNum div 26 –整数除算–上の桁
set lowNum to oNum mod 26 – 1 –余剰計算–下の桁
–つじつま合わせ処理 【強引】
if lowNum = -1 then
set upNum to upNum – 1
set lowNum to 25
end if
set upChar to (item upNum of upperDigitEncTable) as string
set lowChar to (item (lowNum + 1) of lowerDigitEncTable) as string
set resText to upChar & lowChar
end if
return resText
end numAdrToColumnEncode
–Numbersの横方向アドレス（A～Zの26進数）文字列を10進数に変換
on colAddrToNumDecode(origStr)
return aNthToDecimal(origStr, {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}) of me
end colAddrToNumDecode
–n進数文字列を10進数に変換する
on aNthToDecimal(origStr, nTh)
set resNumber to 0
set sList to reverse of (characters of origStr)
set aLen to length of nTh
set digitCount to 0
repeat with i in sList
set j to contents of i
set aRes to offsetInList(j, nTh) of me
set resNumber to resNumber + (aLen ^ digitCount) * aRes
set digitCount to digitCount + 1
end repeat
return resNumber as integer
end aNthToDecimal
on offsetInList(aChar, aList)
set anArray to NSArray’s arrayWithArray:aList
set aInd to (anArray’s indexOfObject:aChar)
if aInd = current application’s NSNotFound or (aInd as number) > 9.99999999E+8 then
error "Invalid Character Error"
else
return (aInd as integer) + 1 –0 to 1 based index conversion
end if
end offsetInList
end script
|
