21時入眠のブログ

誰かを豊かにできる記事を気ままに書いていきまっせ

Excel：できたぁぁぁー！！　関数での配列自動変換！

エクセル初級者の当方でございますが、やっと、やーっと、配列の自動変換ができました！！

いやー、めんどかったーーー！！！

仕事としては、ITスキルはそこまで求められないのですが、
色々と小難しい計算とかをするのでExcelのスキルは必須となっております。

しかも、色々と臨機応変に対応しなければならず、
可能であれば最小限の入力で、いっぺんに済ませたいもの！

今回やったのは、
「元の表」から「実際に使うテンプレ」へのセルの参照です。

実感しましたよ。
配列を作るのって、本当に面倒なんですね……。

備忘録を兼ねて、ブログ記事にしちゃいます！

 

 


☑　そもそも何がしたかったのか？

「元の表から任意の項目」を抜き出して、現場で使う記録用の表を作りたかったんですよ。

 

記録用の表は印刷するので、必要最低限の情報を抽出しないとプリントアウトした時に
虫眼鏡が必要になってしまいます。

 

んで。
その必要最低限の項目とやらが厄介でして、
項目の内容が、増えたり変わったりするんです。

８割はいつも通りなのですが、残りの２割がめんどくさい。

２割といえば、５回に１回ですからね。
その度に適宜修正するのは面倒です。

 

なので、
元の表から項目名だけを、記録用の表へコピペすれば自動でセル参照される、
というのを作りたかったんです。

となれば、XLOOKUP関数のご登場というわけです。

※ちなみに、諸事情からスピルや表のテーブル化ができないので、
それ用に関数を組みました。

 


☑　使用した関数と、その目的

・XLOOKUP(検索値、検索範囲、戻り値、見つからなかった場合)
　　検索して、検索した値から”何を返すのか”をやってくれる関数

・MATCH(検索値、検索範囲、一致条件)
　　検索して、検索範囲の”相対的な位置番号を返す”関数
　　ただし、返すのは１次元となるので注意（要は上から何番目、または左から何番目のどっちか）
　　項目名を判定させ、該当する項目の列番号(横軸の番号)を返させる

・ROW関数(参照セル)
　　参照セルの行番号を返す(要は、参照セルの縦軸の番号を返す)
　　戻り値の行番号を返させる

・ADDRESS(行番号、列番号、参照の仕方)
　　入力された行・列番号から、該当するセル番号を返す
　　戻り値の範囲となる、セル番号を返させる

・CONCATENATE(結合対象文字)
　　関数内の情報を結合して、文字列として出力する
　　戻り値の範囲を作成させる

・INDIRECT(参照文字列)
　　関数内の情報を結合してセル番号として認識し、該当するセルの情報を出力する
　　CONCATENATEで結合した文字列状態の配列を、数式状態にする

関数の説明は細かいところが抜けていたり違っていたりしますが、
記事の趣旨じゃないので気にせんで下さい。

 


☑　実際に組んだ関数はどんな感じ？

動画を載せられればいいのですが、
私のブログ技量やコンテンツ編集がクソ雑魚なため、画像で勘弁して下さい。

 

まず、表の全体像はこんな感じです。

f:id:slow-life-world:20201228193819j:plain

 

 

で、関数は。

f:id:slow-life-world:20201228193746p:plain

f:id:slow-life-world:20201228194522p:plain

=XLOOKUP($J4:$J8,$B$4:$B$13,INDIRECT(CONCATENATE(ADDRESS(ROW($B$4),MATCH(K$3,$B$3:$H$3,0)+1,4),":",ADDRESS(ROW($B$13),MATCH(K$3,$B$3:$H$3,0)+1,4))),"ありません")

 

わかりやすく区切ると、

=XLOOKUP(
検索値
【$I4:$I8】

検索範囲
【$A$4:$A$13】

戻り値【INDIRECT(CONCATENATE(ADDRESS(ROW($B$4),MATCH(K$3,$B$3:$H$3,0)+1,4),":",ADDRESS(ROW($B$13),MATCH(K$3,$B$3:$H$3,0)+1,4)))】

見つからなかった場合
【"ありません"】

 

MATCH関数のすぐ後ろに「＋１」があるのは、
検索範囲である項目欄ですが、【B3:H3】となっているため、
ADDRESS関数で取得する番号が１つ右にずれるためです。

検索範囲をA3セルまで含まれば「＋１」は不要です。 

 

MATCH関数が「文字列」検索をして、「数字」を返すという
特性を持っているのがポイントになりますね。

 

※XLOOKUPの検索値を範囲にしているので、下のセルはゴーストとなっていますが、
実物はセルの結合などが必要だったのでスピルとわけわからん状態となり、
一つ一つ入力する羽目となりました。

 

 

☑　最後に、組んでみた感想を

Excelの関数を組むことはそこそこあるのですが（特にIF関数）、関数で配列を組むというのは初めての試みでして。

 

まあ、めっちゃ大変でした。

 

最初はMATCH関数が”判定”の機能を持っていることを忘れていたので、
わざわざIF関数で無駄に判定させたりもしていましたし。

 

あと、知識としてADDRESS関数やMATCH関数の存在は知っていましたが、
どちらも”あんま使いどころねぇなぁ”とか思っていましたよ。

 

特にADDRESS関数なんて、”なんでこれ数字入力？”とか思っていましたが、
配列を組むのにはめちゃくちゃ重宝しますね、これ。

 

本来？というか、表をテーブルにできればXLOOKUP関数だけで簡単にできるのですが、
そうはいかないこともあるのが世の常といいますか。

 

他にもこんな感じのマイナー情報を発信できたらいいなと思っております。

 

ではでは～。