かExcelに関するアクティビティのうち、行や列の範囲で編集するアクティビティについてまとめました。
範囲の編集で、途中に行や列を追加/削除することは、頻繁にありますよね。
それを自動化できるようになると、身近な作業の効率化に繋がることでしょう。
この記事では、以下のアクティビティについて解説します。
範囲 - 行・列のアクティビティ
- 行を挿入
- 行を削除
- 重複を削除
- 列を挿入
- 列を削除
- VLOOKUP
- MATCH関数
行を挿入
選択したシート、範囲または表内に行を挿入します。(ガイドはこちら)
任意の場所に空白行を追加します。
ここでは、以下の項目について解説します。
- 表または範囲
- 行数
- 先頭行をヘッダーとする
- 場所
- 行番号
表または範囲
行を挿入する対象となる、範囲やテーブル、シートを指定します。
シートを指定する場合、範囲やテーブルがシートの1行目から始まる必要があります。
行数
追加する行数を指定します。
先頭行をヘッダーとする
[表または範囲]で選択した範囲の先頭行が、項目名であればチェックONにしてください。
シートを指定すると、1行目が項目名（ヘッダー）と認識します。
項目名が１行目にない場合は、[表または範囲]にシートではなく、範囲やテーブルを指定してください。
なお、指定した範囲がテーブルである場合は、チェックのON/OFF関係なく先頭行がヘッダーと認識します。
場所
[場所]は、以下3つの中から選択します。
- 開始
- 終了
- 特定
それぞれの違いについて、見ていきましょう。
開始
表の先頭に空白行を追加します。
シートまたは範囲が対象の場合、ヘッダーのチェックONであれば指定した範囲の2行目に、OFFであれば1行目に空白行を追加します。
テーブルの場合は、ヘッダーのチェックON/OFF関係なく項目行の下に空白行が追加されます。
| 対象 | ヘッダーチェックON | ヘッダーチェックOFF |
| シート |  |
 |
| 範囲 |  |
 |
| テーブル |  |
 |
終了
表の一番下に空白行を追加します。
ただし、テーブルの場合は追加されず、何も起こりません。
特定のインデックス
表の中の、指定した行に挿入する場合は、[特定のインデックス]を選択してください。
選択後、[行番号]が表示されるので、何行目かを指定しましょう。
行番号は、範囲やテーブルの行数を超える数値を入力するとエラーになります。
行を削除
シート、表、または範囲から指定した行を削除します。(ガイドはこちら)
指定した範囲から、行を削除します。
全ての項目(列)が同じ値の場合に、重複行を削除する機能も実装されていますね。
ここでは、以下の項目について解説します。
- 表または範囲
- 削除対象
- 位置
- 先頭行をヘッダーとする
表または範囲
行を削除する、範囲やテーブル、シートを指定します。
シートを指定する場合、範囲やテーブルがシートの1行目から始まる必要があります。
削除対象
削除する対象の行を指定します。
以下の4つのうちの、いずれかを選択します。
- 特定の行
- 全ての可視行
- 全ての非表示の行
- すべての重複行
特定の行
削除する行番号を、任意に指定します。
また、"特定の行”を選択した時点で、[位置]が表示されます。
位置
削除したい行番号を入力してください。
ここで注意点がひとつ。
[先頭行をヘッダーとする]が表示されていませんね。
と言うことはつまり、対象がシートや範囲の場合でも、先頭行が項目名(ヘッダー)であるかどうかは判断しないということです。
例えば、項目名がある表の場合で、2番目のデータを削除したい場合は、[位置]には"3"と入力してください。
対象が"シート”で、かつ、対象がテーブルの場合、[位置]に"1"を入力するとエラーになります。
対象がテーブルの場合は、項目行(ヘッダー)は考慮され、データ部分から1行目と判定されます。
[位置]は、1行だけでなく複数の指定も可能です。
入力方法は、以下のとおり。
［位置］の入力方法
- 複数の行番号を指定…コンマ(，)区切りで行番号を入力
- 行番号の範囲を指定…ハイフン(－)で行番号の範囲を入力
入力例
1行、3行、5行を削除…1，3，5
3～5行を削除…3－5
1～3行、5～7行を削除…1－3，5－7
”－"(ハイフン)や"，"(コンマ)について、見やすいよう全角で表記していますが、実際は半角入力です。
表の行数が5行しかない場合に6以上の値を指定すると、エラーになります。
すべての可視行
フィルター設定されている表で、表示されている行を削除します。
"すべての可視行”を選択すると、[先頭行をヘッダーとする]が表示されます。
先頭行をヘッダーとする
チェックONにすると、[表または範囲]で指定した範囲の先頭行をヘッダーとみなし、削除しません。
すべての非表示の行
フィルター設定されている表で、非表示となっている行を削除します。
すべての重複行
重複している行を削除します。
重複する列を指定することはできず、全ての列で値が一致している場合のみとなります。
列を指定する場合は、[重複を削除]アクションを使用してください。
重複を削除
重複行を削除します。(ガイドはこちら)
対象の範囲について、特定の列をキーに重複行を削除します。
Excelの[重複の削除]と同じ機能ですね。
ここでは、以下の項目について解説します。
- 範囲
- 先頭行をヘッダーとする
- 比較対象の列
項目名と説明で、"範囲"という名前が被っています。説明内での"範囲"は、"A1:E11"のようにセル範囲を指定するという意味です。項目名の場合は[範囲]のように大かっこで挟んでいます。
範囲
重複行を削除する、範囲やテーブル、シートを指定します。
シートを指定する場合、範囲やテーブルがシートの1行目から始まる必要があります。
１行目からデータが始まらない場合、シートで指定するのではなく、範囲を指定してください。
また、テーブルを対象とする場合は、シートや範囲で指定するのではなく、テーブルで指定することをお勧めします。
テーブルをシートや範囲で指定しても、動作はします。
ですが、テーブルが配置している位置や指定方法によってエラーになるため、ここは素直にテーブルにした方がいいでしょう。
先頭行をヘッダーとする
[範囲]で選択した範囲の先頭行が、項目名であればチェックONにしてください。
なお、[範囲]でテーブルを指定した場合は、チェックONにしましょう。
テーブルでチェックONにしないと、エラーになります。
比較対象の列
重複が含まれている列を指定します。
最低1つは追加する必要があります。
[列を追加]を複数回クリックして複数の列を指定することも可能。
列を挿入
シート、表、または範囲の指定した位置に新しい列を挿入します。(ガイドはこちら)
列を追加するアクティビティです。追加と同時に、書式の設定ができます。
ただし、１列分しか追加できません。
ここでは、以下の項目について解説します。
- 範囲
- 先頭行をヘッダーとする
- [場所]と[列基準]
- ヘッダーを追加
- データの書式設定の種類
項目名と説明で、"範囲"という名前が被っています。説明内での"範囲"は、"A1:E11"のようにセル範囲を指定するという意味です。項目名の場合は[範囲]のように大かっこで挟んでいます。
範囲
列を挿入する対象となる、範囲やテーブル、シートを指定します。
シートを指定する場合、範囲やテーブルはシートの1行目から始まる必要があります。
先頭行をヘッダーとする
[範囲]の先頭行が項目名であればチェックONにしてください。
シートを指定すると、1行目を項目名（ヘッダー）と認識します。
項目名が１行目にない場合は、[範囲]にシートではなく、範囲やテーブルを指定してください。
[場所]と[列基準]
追加する場所を[列基準]で指定し、列の追加を前にするか後ろにするかを[場所]で指定します。
レイアウトからすると順番が逆ですが、まず[列基準]の説明から。
[列基準]は、空白列を追加する位置を指定します。
[先頭行をヘッダーとする]がチェックONの場合は、メニューに列名が表示されますので、その中から選択しましょう。
(テーブルの場合は、ON/OFF関係なく項目名になります。)
[先頭行をヘッダーとする]がチェックOFFの場合、メニューには列番号が表示されます。
そして[場所]は、"前"と"後”から選択します。
ヘッダーを追加
列を追加すると同時に、項目名を設定できます。
値を指定しない場合、対象が範囲であれば空白が、テーブルであれば「列n」(nは数値)という項目名が自動設定されます。
[先頭行をヘッダーとする]がチェックOFFでも1行名に項目名が追加されるので、その場合は値をクリアしましょう。
データの書式設定の種類
列を追加すると同時に、その列の書式を設定できます。
[書式設定]をクリックして、どの書式にするかを選択します。
列を削除
シート、表、または範囲の指定した列を削除します。(ガイドはこちら)
列を削除します。
ヘッダー付の範囲やテーブルでは1行の削除しかできませんが、シートや範囲でヘッダーがない場合は、複数列の削除が可能になります。
ここでは、以下の項目について解説します。
- ソース
- [先頭行をヘッダーとする]と[列名]
ソース
列を削除する対象となる、範囲やテーブル、シートを指定します。
[先頭行をヘッダーとする]と[列名]
[ソース]の先頭行が項目名であれば、チェックONにしてください。
シートを指定すると、1行目を項目名（ヘッダー）と認識します。
チェックONの場合はメニューに項目名が表示されますので、その中から選択しましょう。
(テーブルの場合は、ON/OFF関係なく項目名になります。)
複数の列を削除する
複数列を削除したい場合は、以下の要件を満たす必要があります。
複数列指定の条件
- (テーブルでない)シートまたは範囲
- [先頭行をヘッダーとする]がチェックOFF
- [列名]を[テキスト]で編集する
家電表(A1：E6)のB～D列を削除する場合を例に見てみましょう。
複数列を削除するには、以下のように編集します。
[テキスト]の入力方法は、以下のとおりです。
［テキスト］の入力方法
- 複数の列を指定…コンマ(，)区切りで列名を入力
- 複数の列を指定…コロン(：)で開始列名と終了列名を入力
入力例
A列、C列、E列を削除…A，C，E
C～E列を削除…C：E
A～C列、E～G列を削除…A：C，E：G
”："(コロン)や"，"は見やすいよう全角で表記していますが、実際は半角入力です。
VLOOKUP
ExcelのVLOOKUP関数を使用して範囲またはシート内のデータを検索します。(ガイドはこちら)
対象の値を表の１列目から検索し、値にマッチする行が見つかったら、その行の任意の(列の)値を返します。
例えば、対象のidをマスタ表から検索し、その価格を取得する場合を例に、図解しました。
VLOOKUPはExcelの表を扱う場合、必ずと言っていいほど出てくる数式ではないでしょうか。
ここでは、以下の項目について解説します。
- 検索する値
- 対象範囲
- 列インデックス
- 完全一致
- 保存先
検索する値
検索したい値を指定します。
対象範囲
検索する対象となる、範囲やテーブル、シートを指定します。
検索するキーは、必ず1列目であることが条件です。
この点は、ExcelのVLOOKUPと変わりありませんね。
列インデックス
[対象範囲]から検索でマッチした行の、何列目の値を返すかを指定します。
入力を省略すると、最後の列の値が対象になります。
完全一致
チェックONにすると、[検索値]と[対象範囲]の1列目が完全一致する場合に限り、値を返します。
チェックONが、ExcelのVLOOKUPで言うFALSEに該当します。
一致する値が見つからない場合は、空白を返します。
Excelのように、エラー値を返さないのは助かりますね。
VLOOKUPの[完全一致]チェックOFF(TRUE)の使いどころ
VLOOKUPで、完全一致をチェックOFF(ExcelではTRUE)にすることはあるのでしょうか？
もしかすると、ExcelでもTRUEに設定したことがない人は、案外多いんじゃないかと思います。
僕もExcel歴は長いものの、しばらくの間、さも当たり前のようにFALSEしか使いませんでした。
しかし、TRUEに設定したほうがいいケースが、実はあります。
結論から言うと、TRUEにすると劇的に処理スピードが速くなるという条件があります。
以下に、条件をまとめます。
TRUEにする場合の条件
- 対象範囲の行数が多い。(数万行)
- 対象範囲の1列目が、昇順にソートされている。
- 検索値が、対象範囲に必ず存在する。
最初の条件の「行数が多い」とは、列数や値のボリュームによって左右されます。
数万件以上が目安で、そのあたりから違いに差が出てきますね。
保存先
返した値を書き込む場所を指定します。
Excelのセル、または、クリップボードを保存先として指定できます。
VLOOKUPの実装例
冒頭の例題を再掲します。
これを、実際にStudioXで組んだ結果がこちらです。
価格の列(B列)に、順に結果を反映させるために、[繰り返し(Excelの各行)]アクションを使うところがポイントですね。
使用しているアクティビティ
MATCH関数
指定した項目をセルの範囲内で検索し、範囲内におけるその項目の相対位置を返します。(ガイドはこちら)
対象の値を一覧から検索し、値にマッチする行が見つかったら、一覧の先頭行(先頭列)から何番目の位置にあるかを返します。
ここでは、以下の項目について解説します。
- 検索値
- 対象範囲
- 照合の種類
- 保存先
検索値
検索したい値を指定します。
対象範囲
検索する対象となる、行や列、あるいはテーブルを指定します。
範囲は、1行、または1列の範囲を指定してください。
もちろん、複数列の表だったとしても、範囲を1行で指定すれば問題ありません。
テーブルの場合は、複数列から1列に絞れないため、1列のテーブルしか指定できません。
照合の種類
[対象範囲]から検索する種類を、以下の3つから選択します。
- 0(検索値と等しい最初の値)
- 1(検索値以下の最大値)
- -1(検索値以上の最小値)
特に"0(検索値と等しい最初の値)"以外の場合、対象範囲を並べ替えていないと正しく検索されません。
必ず並べ替えた後の範囲を指定してください。
照合の種類="1(検索値以下の最大値)"の場合、並びを昇順にしてください。
照合の種類="-1(検索値以上の最小値)"の場合、並びを降順にしてください。
サンプル例題
以下の条件で、[照合の種類]による違いをまとめました。
[検索値]＝"103", [対象範囲]="A1:A4"
| 照合の種類 | 対象範囲=昇順 |
対象範囲=降順 |
| 0(検索値と等しい最初の値) | エラー | エラー |
| 1(検索値以下の最大値) | 2(102の行番号を返す) | エラー |
| -1(検索値以上の最小値) | エラー | 2(104の行番号を返す) |
保存先
返した一番号を書き込む場所を指定します。
Excelのセル、または、クリップボードなどを保存先として指定できます。
まとめ
行や列の編集系アクティビティは、Excelの機能と、少しずつ違いがあります。
StudioXの動きがどうなるのか、一度でも頭に入れておけば、開発で想定外の動作をした時に「あ、そういえば」って思い出すことがあるかもしれません。
まとめます。
まとめ
- 全列の重複削除は[行を削除]、特定の列をキーにして重複削除する場合は[重複を削除]。
- 範囲にシートを指定した場合は、表が1行目から始まること。
- [VLOOKUP]で、”完全一致"のチェックOFFを使うと高速になる。
- [MATCH]関数は、照合の種類と並べ替えの組み合わせに注意。
この記事で、何か１つでも新しい発見が得られたのなら、僕もうれしいです。