こんにちは。萱場です。
今回は、Google スプレッドシート を使って、クロス表のテーブル型の表への組み直しと、クロス集計を行ってみたいと思います。
Google スプレッドシートの紹介はこちらを御覧ください。
背景
弊社では、価格表や、規格表などを仕入先様からもらうことが多いのですが、フォーマットが各社違うので、なにかデータの登録をしようとした際に結構苦労します。
テーブル型の表に直すことでVlookup関数なども使用できるようになり、自作した料金表を参照して、原価計算を簡単にすることもできます。
今回扱う表はこんな感じです。
これは、ボタンの料金表のイメージですが、品番、カラー以下が、ボタンのサイズになっており、縦軸で品番、カラーを探し、横軸でサイズを探し、その重なったところが、そのボタンの金額というような表です。
目で見る分には、わかりやすいのですが、データとしては少し扱いづらいかなと思っています。
今回は上の表を下のように組み替えます。
それでは、やっていきます。
下準備
下準備は、1工程だけです。
品番の列の左側に一行挿入して、品番とカラーを”_(アンダースコア)”でつないだ値をセットします。A3の値であれば下のような感じです。
=B3&"_"&C3下準備はこれだけです。
続いて、表の組み換えを行っていきます。
テーブル型に変換
使用する関数
ここで使う関数は、以下になります 。
ARRAYFORMULA()
配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。
https://support.google.com/docs/answer/3093275?hl=ja
SPLIT()
指定した文字または文字列の前後でテキストを分割し、各部分を同じ行の別のセルに表示します。
https://support.google.com/docs/answer/3094136?hl=ja
FLATTEN()
1 つ以上の範囲に含まれるすべての値を、単一の列にフラット化します。
https://support.google.com/docs/answer/10307761?hl=ja
特に重要なのは、FLATTEN()関数の説明の中で下の方に書いてある以下の部分です。
A列とB列をクロス結合した結果がD〜E列に出力されています。
この関数を元にして以下のように組み合わせます。
=ARRAYFORMULA(SPLIT(FLATTEN(A3:A16&"_"&D2:Q2),"_"))
そうすると、T3セル以下の様に、品番、カラー、サイズがクロス結合された表ができます。
この関数は、”_”でヨコに結合したものを、SPLIT関数で分解して、最後ARRAYFORMULA関数で、値を出力している感じです。品番列、カラー列をそのままクロス集計すると、組み合わせが上手く行かないので、予め、SPLIT関数で区切り文字に指定する”_”で結合しておくというのがポイントになります。
クロス集計
表ができたところで、その金額に該当する値を元のクロス表から撮ってきたいと思います。
下準備
最初と同じ様に、組み替えた表の品番の左側にも品番&”_”&カラーの値を入れておきます。
使用する関数
INDEX()
行と列のオフセットで指定したセルのコンテンツを返します。
https://support.google.com/docs/answer/3098242?hl=ja
MACTH()
指定した値と一致する範囲内のアイテムの相対的な位置を返します。
https://support.google.com/docs/answer/3093378?hl=ja
MATCH関数で、縦、ヨコそれぞれの位置を取得して、それをINDEX関数の引数として値を返すといった感じです。
=INDEX(値の範囲,品番_色の位置をMATCH関数で取得,サイズの位置をMATCH関数で取得)
=INDEX($D$3:$Q$16,MATCH(S7,$A$3:$A$16,0),MATCH(V7,$D$2:$Q$2,0))
そうすると、料金のところにも金額がしっかりと入力されました。
値を固定したければ、値のコピペを行えば完成です。
まとめ
最後までお読みいただきありがとうございました。
値を変更すれば、関数もそのまま使えますし、各関数の公式ドキュメントを読んで、より理解を深めて応用頂く事もできるかと思います。
お役に立てれば幸いです。
株式会社デザインXでは、アパレル業界のお客様に向けて、ECサイトや業務基幹システム(ERP)の開発・導入支援を行っております。ご興味をお持ちの方は、是非下記リンクよりお気軽にお問い合わせください。
