curioshiki’s gists

curioshiki / ExportAllComments.osts

Created April 7, 2025 19:16

Extract All Comments in Excel,using OfficeScript

	function main(workbook: ExcelScript.Workbook) {
	// ブック内の全シートをループ
	let worksheets = workbook.getWorksheets();
	worksheets.forEach(sh => {
	try {
	let comments = sh.getComments();

	// コメント出力用シート同名シートがすでにあれば削除
	let shexName = sh.getName() + "_comments";
	let existingSheet = workbook.getWorksheet(shexName);

curioshiki / extract-memos.vb

Created April 7, 2025 18:35

Extract All Memos in Excel

	Option Explicit

	Sub ExtractMemos()
	Dim cmt As Comments
	Dim c As Comment
	Dim rp As Comment
	Dim sh As Worksheet
	Dim shex As Worksheet
	Dim exLine As Long
	' cmt Comment[s]型シート内のすべてのメモをコレクションとして格納

curioshiki / exract-comments.vb

Last active April 7, 2025 18:17

Export comments in Excel

	Option Explicit

	Sub ExtractComments()
	Dim cmt As CommentsThreaded
	Dim c As CommentThreaded
	Dim rp As CommentThreaded
	Dim sh As Worksheet
	Dim shex As Worksheet
	Dim exLine As Long
	' cmt Comment[s]Threaded型シート内のすべてのコメントをコレクションとして格納

curioshiki / exract-comments-w-subprocedure.vb

Last active April 7, 2025 18:15

Export comments in Excel (with subprocedure)

	Option Explicit

	Sub ExtractComments()
	Dim cmt As CommentsThreaded
	Dim c As CommentThreaded
	Dim rp As CommentThreaded
	Dim sh As Worksheet
	Dim shex As Worksheet
	Dim exLine As Long
	' cmt Comment[s]Threaded型シート内のすべてのコメントをコレクションとして格納

curioshiki / epidweek.txt

Last active January 11, 2025 03:53

Converting Epidemiology Week to Date, Date to Epidemiology Week

	EPIDWEEKTODATE=LAMBDA(Yr, EpidWk,
	LET(
	start, DATE(Yr, 1, 1),
	dow, WEEKDAY(start, 12),
	flag, IF(dow >= 4, 0, -1),
	weekstart, start - dow,
	weekstart + (EpidWk + flag) * 7
	)
	);
	EPIDDATETOWEEK = LAMBDA(Dt,

curioshiki / ave7-roc7-roc14.txt

Last active June 15, 2023 09:45

7-day average, 7-day rate of change, and 14-day rate of change functions for statistical processing

	AVE7DAYS = LAMBDA(range,
	iferror(average(offset(range,-6,0,7,1)),"")
	);
	ROC7DAYS = LAMBDA(range,ifzero,
	if(row(range)>14,iferror(
	if(sum(offset(range,-6,0,7,1))/sum(offset(range,-13,0,7,1))<>0,
	sum(offset(range,-6,0,7,1))/sum(offset(range,-13,0,7,1)),ifzero),
	ifzero),"")
	)
	;

curioshiki / revtext.txt

Last active June 15, 2023 09:45

LAMBDA function =REVTEXT(string)

	REVTEXT = LAMBDA(x,[y],
	if(x<>"",
	if(isomitted(y),
	concat(right(x,1) , REVTEXT(LEFT(x,len(x)-1),len(x)-1)),
	concat(right(x,1) , REVTEXT(LEFT(x,len(x)-1),y-1))
	),
	""
	)
	);

curioshiki / numeraljp.txt

Created March 30, 2022 07:18

LAMBDA numeraljp() for Excel