一.概要
1.予想される効果
同一帳票で、相互に影響を与えることなくグループ別でダイナミック並べ替えしたい場合があります。たとえば、関東での売上をクリックすると、関西での売上に影響はなく、関東での売上の並べ替えが変わります。関西での売上をクリックすると、関東での売上に影響はなく、関西での売上の並べ替えが変わります（下図参照）：
2.考え方
ステップ1：地域の数に従って配列の長さを定義し、配列内の要素で対応するグループを昇順で格納します（1は昇順、-1は降順）。
ステップ2：売上をクリックする時は、配列内のクリック位置に対応する値をクリアしてから、反対の数値になる値を配列内の対応する位置に挿入します。
ステップ3：展開後に並べ替えを設定し、まずクリック位置に対応する配列の値を取得し、次に売上が配置されているグループのセルの値を掛けて、正・負の昇順で再度並べ替えます。
二.例示
1.データの準備
一般の帳票を作成し、データベースクエリds1を作成します。
SQL文：SELECT * FROM 販売量 where 商品名称 = 'アップルジュース'。
2.帳票の設計
セルA1〜D1を結合し、地域データ列をセルA1にドラッグし、セルA2〜D2について下図に示すようなタイトルを入力し、セルA3〜D3を対応するデータ列にドラッグします。表のスタイルを調整します。効果は下図のとおり:
3.動的パラメータの設定
セルD2を選択し、ハイパーリンク- ダイナミックパラメータを追加します。パラメーター名はaであり、パラメーター値に数式タイプを選択し、以下の式を入力します。
JOINARRAY(ADD2ARRAY(REMOVEARRAY(IF(LEN($a) = 0, RANGE(COUNT(A1[!0]))*0, SPLIT($a, ",")), &A1, 1),
IF(INDEXOFARRAY(SPLIT($a, ","), &A1) = 1, -1, 1), &A1),",")
動的パラメーターの機能は、売上をクリックすると、式で定義されたルールに従ってパラメーターを返し、パラメーターに従って並べ替えることができることです。
式の説明を次に示す：
1.ステップ1：IF(LEN($a) = 0, RANGE(COUNT(A1[!0]))*0, SPLIT($a, ","))
式 | 説明 |
|---|---|
LEN($a) = 0 | 帳票を初期化するとき、パラメータaは空である。つまり、長さは0に等しく、初めてクリックしたら割り当てられる |
RANGE(COUNT(A1[!0]))*0 | セルA1の展開された値を合計し、値と同じ長さの配列を定義し、それを0で乗算することで、並べ替えが初めてクリックされたときにパラメーター値が変更できる |
SPLIT($a, ",") | パラメータは転送中に自動的に文字列に処理されるため、ここでSPLITで、パラメータaを,で文字列配列に分割する必要がある。 |
2.ステップ2：REMOVEARRAY(ステップ1の結果, &A1, 1)
式 | 説明 |
|---|---|
| REMOVEARRAY(ステップ1の結果, &A1, 1) | &A1でクリックされたグループの位置を取得し、ステップ1で返された配列内のその位置の値を削除して、削除された配列を返す。つまり、配列内のクリック位置に対応する値が削除される |
3.ステップ3：ADD2ARRAY(ステップ2の結果, IF(INDEXOFARRAY(SPLIT($a, ","), &A1) = 1, -1, 1), &A1)
式 | 説明 |
|---|---|
INDEXOFARRAY(SPLIT($a, ","), &A1) | ＆A1でクリックされたグループの位置を取得し、コンマで分割された文字列配列内のその位置の値を返す。 |
IF() | IFで配列の値を判断し、1と-1で順序を変更する目的を達成する。つまり、クリックするたびに元の値が反対の値に変更される |
ADD2ARRAY() | ＆A1でクリックされたグループの位置を取得し、ステップ2で配列内のその位置を返し、反対値に変更された配列内の全ての要素を挿入してから、当配列を返す。 |
4.ステップ４：JOINARRAY(ステップ3の結果, ",")
式 | 説明 |
|---|---|
| JOINARRAY(ステップ3の結果, ",") | ダイナミックパラメーターは配列タイプの配列を返すことができないため、配列関数でステップ3の結果を,を区切り文字として持つ文字列に変換する必要がある。 |
手順は下図の通りです：
4.拡張後の並べ替え
セルA3を選択し、展開後に並べ替えを設定し、昇順を選択して、数式を入力します：D3 * INDEXOFARRAY(split($a、","), &A1)。
INDEXOFARRAY(split($a, ","), &A1)とはクリックされたグループの位置を&A1で取得し、コンマで分割された文字列配列内のその位置の値(1または-1)を返します。
次に、セルD3の値に1または-1を掛けます。正の数で昇順する場合、売上列は昇順になり、負の数で昇順する場合、売上列は降順になります。
手順は下図の通りです：
5.プレビュー
5.1.PC端末
帳票を保存し、改ページプレビューをクリックしてください。
5.2.モバイル端末
APPとHTML5端末でプレビューでき、効果は下図のようになります。