ひとまずはやってみましょう。まずは WEBSERVICE関数を使う方法です。
「Excel2013〜2019」と「Excel for 365」とでやり方が違ってきます。
APIは「
Holidays JP API」を使わせてもらいます。
■A1：2021
※表示させたい「年」を入力
★Excel2013〜Excel2019の場合
■A2
<![CDATA[=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(]]>
<![CDATA[WEBSERVICE("https://holidays-jp.github.io/api/v1/"&$A1&"/date.json"),]]>
<![CDATA["{","<x><y>"),"}","</y></x>"),"""",""),":",","),",","</y><y>")]]>
■B2
=IFERROR(INDEX(FILTERXML(A$2,"//y"),ROW()*2-3),"")
表示形式を「日付」に設定
■C2
=IF(B2="","",INDEX(FILTERXML(A$2,"//y"),ROW()*2-2))
B2:C2を選択して下方向多めにコピー
★Exce2021の場合
■B2
<![CDATA[=LET(_year,A$1,_arry,]]>
<![CDATA[FILTERXML(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(]]>
<![CDATA[WEBSERVICE("https://holidays-jp.github.io/api/v1/"&_year&"/date.json"),]]>
<![CDATA["{","<x><y>"),"}","</y></x>"),"""",""),":",","),",","</y><y>"),"//y"),]]>
<![CDATA[INDEX(_arry,SEQUENCE(ROWS(_arry)/2,2)))]]>
★Microsoft365の場合
<![CDATA[=LET(_year,A1,]]>
<![CDATA[_ws,WEBSERVICE("https://holidays-jp.github.io/api/v1/"&A1&"/date.json"),]]>
<![CDATA[WRAPROWS(FILTERXML("<x><y>"&TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(]]>
<![CDATA[REPLACE(LEFT(_ws,LEN(_ws)-2),1,2,""),"""",""),":",","),",","</y><y>"))]]>
<![CDATA[&"</y></x>","//y"),2))]]>
以上です。JSON形式のファイルは下のような形なので 数式で抜き出すのは若干面倒です。
TEXTSPLIT関数が使えるようになれば もっと短くできるんですが。
ただこれだと参照する URLが固定になってしまうので 毎年の更新が面倒です。
先程 名前定義した「年」の値をパラメータにしてしまいましょう。
[ホーム]→[詳細エディタ]
「ソース」の上に黄色の行を追加します。
年 = Number.ToText(Excel.CurrentWorkbook(){[Name="年"]}[Content]{0}[Column1]),
続いて URLの「年」のところに変数を当て込みます。
ソース = Json.Document(Web.Contents("https://holidays-jp.github.io/api/v1/" & 年 & "/date.json")),
→[完了]
以上でパラメータクエリの完成です。今回はこっちの方が簡単だったのでそうしましたが Web.Contentsは本来「RelativePath」や「Query」を指定して変数を代入します。
[ホーム]→[閉じて読み込む]→[閉じて次に読み込む]
データを返す先を「B1」として[OK]
これで読み込み完了です。
読み込んだテーブルにはこのように表示されます。
もし「
暦API」の APIでやる場合は
let
年セル = Excel.CurrentWorkbook(){[Name="年"]}[Content]{0}[Column1],
年 = if 年セル = null then Date.Year(DateTime.LocalNow()) else 年セル,
URL = "http://koyomi.zingsystem.com/api/",
Webコンテンツ = Web.Contents(URL, [Query =
[mode="m", cnt="12", targetyyyy=Number.ToText(年), targetmm="1"]]),
Jsonドキュメント = Json.Document(Webコンテンツ),
日付リスト = Record.ToTable(Jsonドキュメント[datelist]),
列の展開 = Table.ExpandRecordColumn(日付リスト, "Value", {"holiday"}),
日付型に変更 = Table.TransformColumnTypes(列の展開, {"Name", type date}),
列名の変更 = Table.RenameColumns(日付型に変更,
{{"Name", "日付"}, {"holiday", "祝日名"}}),
不要な行を削除 = Table.SelectRows(列名の変更, each [祝日名] <> "")
in
不要な行を削除
こんな感じです。
もし複数年分のデータが欲しい場合は２つのクエリを縦結合してやればいいです。
let
当年セル = Excel.CurrentWorkbook(){[Name="年"]}[Content]{0}[Column1],
当年 = if 当年セル = null then Date.Year(DateTime.LocalNow()) else 当年セル,
URL = "https://holidays-jp.github.io/api/v1/★/date.json",
当年URL = Text.Replace(URL, "★", Number.ToText(当年)),
翌年URL = Text.Replace(URL, "★", Number.ToText(当年+1)),
年テーブル = Record.ToTable(
Json.Document(Web.Contents(当年URL))
& Json.Document(Web.Contents(翌年URL))
),
型を変換 = Table.TransformColumnTypes(年テーブル, {"Name", type date})
in
型を変換
クエリの便利なところは結合や追加が簡単にできることですね。
例えば会社独自の「特別休暇一覧」を別に作成しておけば それらをマージすることもできます。
(当年 as number) =>
let
URL = "https://holidays-jp.github.io/api/v1/★/date.json",
当年URL = Text.Replace(URL, "★", Number.ToText(当年)),
年テーブル = Record.ToTable(
Json.Document(Web.Contents(当年URL))
),
列名の変更 = Table.RenameColumns(年テーブル,
{{"Name", "日付"}, {"Value", "名称"}}),
型の変更 = Table.TransformColumnTypes(列名の変更,
{{"日付", type date}, {"名称", type text}})
in
型の変更
上記のようなカスタム関数を作っておいて
let
当年セル = Excel.CurrentWorkbook(){[Name="年"]}[Content]{0}[Column1],
当年 = if 当年セル = null then Date.Year(DateTime.LocalNow()) else 当年セル,
祝日一覧結合 = 祝日一覧(当年-1) & 祝日一覧(当年) & 祝日一覧(当年+1)
in
祝日一覧結合
こんな感じにしてもいいと思います。List.Generateで結合してもいいです。
[テーブルへの変換]をクリック
