2022年01月19日
選択されたセルの内容を列ごとにソート（昇順・降順）
Numbersで選択したセルを列ごとに昇順・降順ソートするAppleScriptです。
セルを選択して実行すると並び替えが行われます。
テキストベースでの値の処理です。
昇順ソート
use AppleScript version "2.7"
use framework "Foundation"
use scripting additions
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 selList to name of (every cell of selection range)
set columnCount to count of (column of selection range) --列をカウント
set rowCount to count of (row of selection range) --行をカウント
set startCellList to items 1 thru columnCount of selList
set aList to {}
set bList to {}
repeat with i in startCellList
set topCellStr to my regexReplace((contents of i), "\\d", "")
set topCellNum to my regexReplace((contents of i), "[a-zA-Z]", "")
repeat with ii from 1 to rowCount
set the end of aList to (topCellStr & (topCellNum - 1) + ii)
end repeat
set the end of bList to aList
set aList to {}
end repeat
bList
repeat with ii in bList
set cellsNameList to contents of ii
set aRange to ((first item of cellsNameList) & ":" & (last item of cellsNameList))
set aRes to value of every cell of range aRange
set selectCellNo to name of every cell of range aRange
set cList to sort1DList_ascOrder_(aRes, true) of me
repeat with j from 1 to (length of selectCellNo)
set value of cell (item j of selectCellNo) to item j of cList
end repeat
end repeat
end tell
end tell
end tell
end tell
--1D Listをsort / ascOrderがtrueだと昇順ソート、falseだと降順ソート
on sort1DList:theList ascOrder:aBool
set aDdesc to current application's NSSortDescriptor's sortDescriptorWithKey:"self" ascending:aBool selector:"compare:"
set theArray to current application's NSArray's arrayWithArray:theList
return (theArray's sortedArrayUsingDescriptors:{aDdesc}) as list
end sort1DList:ascOrder:
--正規表現でテキストを置き換え
on regexReplace(aText as text, pattern as text, replacement as text)
--require framework: Foundation
set regularExpression to current application's NSRegularExpression's regularExpressionWithPattern:pattern options:0 |error|:(missing value)
return (regularExpression's stringByReplacingMatchesInString:aText options:0 range:{location:0, |length|:count aText} withTemplate:replacement) as text
end regexReplace
降順ソート
use AppleScript version "2.7"
use framework "Foundation"
use scripting additions
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 selList to name of (every cell of selection range)
set columnCount to count of (column of selection range) --列をカウント
set rowCount to count of (row of selection range) --行をカウント
set startCellList to items 1 thru columnCount of selList
set aList to {}
set bList to {}
repeat with i in startCellList
set topCellStr to my regexReplace((contents of i), "\\d", "")
set topCellNum to my regexReplace((contents of i), "[a-zA-Z]", "")
repeat with ii from 1 to rowCount
set the end of aList to (topCellStr & (topCellNum - 1) + ii)
end repeat
set the end of bList to aList
set aList to {}
end repeat
bList
repeat with ii in bList
set cellsNameList to contents of ii
set aRange to ((first item of cellsNameList) & ":" & (last item of cellsNameList))
set aRes to value of every cell of range aRange
set selectCellNo to name of every cell of range aRange
set cList to sort1DList_ascOrder_(aRes, false) of me
repeat with j from 1 to (length of selectCellNo)
set value of cell (item j of selectCellNo) to item j of cList
end repeat
end repeat
end tell
end tell
end tell
end tell
--1D Listをsort / ascOrderがtrueだと昇順ソート、falseだと降順ソート
on sort1DList:theList ascOrder:aBool
set aDdesc to current application's NSSortDescriptor's sortDescriptorWithKey:"self" ascending:aBool selector:"compare:"
set theArray to current application's NSArray's arrayWithArray:theList
return (theArray's sortedArrayUsingDescriptors:{aDdesc}) as list
end sort1DList:ascOrder:
--正規表現でテキストを置き換え
on regexReplace(aText as text, pattern as text, replacement as text)
--require framework: Foundation
set regularExpression to current application's NSRegularExpression's regularExpressionWithPattern:pattern options:0 |error|:(missing value)
return (regularExpression's stringByReplacingMatchesInString:aText options:0 range:{location:0, |length|:count aText} withTemplate:replacement) as text
end regexReplace
更新履歴
- 2022年01月19日新規掲載ラベル
- 20xx年xx月xx日更新情報が入りますラベル
- 20xx年xx月xx日更新情報が入りますラベル
- 20xx年xx月xx日更新情報が入りますラベル
- 20xx年xx月xx日更新情報が入りますラベル