Excel：COUNTIF関数で "true"、"false" を扱うときの注意

Excelの COUNTIF 関数は、アンケート結果の集計などで良く使う関数かと思います。

　　=COUNTIF(範囲, 検索条件）

という書式で、検索条件に指定した条件を満たすセルの個数を求めます。

 =COUNTIF(M2:M300, "はい"）
 =COUNTIF(M2:M300, true)
のような感じで使用することが多いのですが、この true や false（論理値）の場合は注意が必要です。

Excelシートから直接、true や false を論理値入力する場合は良いのですが、文字列として ’true や ’false を入力した場合や、[セルの書式設定]-[表示形式] が「文字列」になっている場合や、他のアンケート収集データを連携したために、元々 "true" や "false" と文字列になっている場合は、

  =COUNTIF(M2:M300, "true")

  =COUNTIF(M2:M300, "false")

としても、該当セルをカウントしてくれません。Excelの COUNTIF関数は、"true" と "false" という文字列に対して、特別な扱いをするようです。

このため、あまり納得のいく書き方ではありませんが、以下のように両端に「*」を付加することにより、該当セルをカウントできます（もちろん、"true123" や "123false" もカウントしてしまいますが）。

  =COUNTIF(M2:M300, "*true*")

  =COUNTIF(M2:M300, "*false*")

Excel：ブックの破損

回復確認のダイアログが出る。

高速エンジンを使用して生成した Excel で、ファイルを開こうとしたら上図のようなダイアログが出たことはないでしょうか。考えられる原因は幾らかありますが、大抵の理由は以下のとおりになります。確認してみてください。

ひな形行、あるいはひな形列上に名前定義がある。：名前定義が不正に削除される。
名前定義に、マクロ関数を割り当てたものがある。：高速エンジンが解析できない。
名前定義の定義名に、記号が含まれたものがある。：高速エンジンが解析できない。
生成するシートの名称に、カッコが含まれている。：高速エンジンが解析できない。

Designer：繰り返し実行指定ファイルの中身が空のときは？

「手順の繰り返し実行」を設定した手順で、繰り返し指定ファイル（パラメーターリスト）の中身が空のときはどうなるでしょうか？

上図のようにデフォルト設定では、その繰り返し手順は警告終了となりますので、後続手順が実行されます。

繰り返し指定ファイルの中身が空のときに、後続手順を実行させたくないときは、以下のように後続手順の [判定条件] 設定で、[先行手順の実行結果が次のとき実行する] の [警告終了] を「いいえ」に設定します。 

Designer：ユーザーの一時フォルダにサイズの大きな .tmp ファイルが作成されるとき

現象

xoBlosを実行すると、ユーザーの一時フォルダ
　　C:\Users\＜ユーザー名＞\AppData\Local\Temp
にサイズの大きな .tmp ファイルが作成される。

原因と対処方法

「Excel 表から抽出」手順で、サイズの大きな抽出対象ファイルをCSV形式で指定している場合は、その手順を見直してみましょう。

CSV形式からの抽出で、高速エンジンの使用が「はい」になっている場合は、「いいえ」で設定してみてください。「Excel 表から抽出」手順では、CSVファイルを一旦 Excelファイルとして処理するため、.tmp ファイルが作成されてしまうことがあります。

CSVファイルをXMLファイルに変換するには、「変換（CSV→抽出データ）」手順やxobQueryを使用する方法もありますので、検討してみてください。

Designer：『非出力項目』の役割

どういう時に使うの？

データ抽出の際、作成される XML ファイル内に不要な項目があれば、キーワード『非出力項目』を「yes」にすることで、出力を阻止することができます。上図で、もし「その他情報」が要らない場合は、「その他情報」の列に「yes」を設定します。

「抽出するのに不要とはどういうこと？」

「最初から出力項目の対象としなければ良いじゃない！」

ご意見は尤もです。しかし、上図のように処理値として扱う際には、明示が必要です。

抽出するデータの条件として「その他情報」が空白であるものを設定（空白でないものをスキップ）しています。更には『取得データの組合せ』で、データ情報を加工したものを出力しようとしています。「その他情報」その物は不要としていますが、判定等で使う場合には具体明示しなければなりません。

「不要情報も XML ファイル上にあるだけで、生成時に使わなければ良いじゃない！」

こういったご意見もあるかと思います。しかし大量データで、一項目減るだけで容量が大きく削減できるのであれば、持たないことが望ましいです。また、CSV に変換して別のシステムにあて込むという要件があった場合には、列構成を合わせる為に余計な情報は確実除去しなければならない――というケースも充分あり得ます。

Designer＋Windows：ピボット作成時に (空白) カテゴリーを作らない方法

次の記事の補足情報です。

Designer：ピボット処理の自動実行
http://xoblos.hatenablog.jp/entry/2017/08/19/233737

上記の記事の方法でピボットテーブル、ピボットグラフを作成すると、ひな形データシートの3行目までを範囲とするため、余分な (空白) カテゴリーができてしまいます。ピボット集計上は何ら問題ないため、そのままにするか、気になる場合は軸フィールドのフィルタリングでオフにする方法で問題ないと思います。

どうしても (空白) カテゴリーを作りたくない場合は、下記の2つの方法があります。1. の方法は、処理速度が遅いため、データ量が多いときはお勧めではありません。

1. データシートの最終行を #S 記号で削除する方法
2. 外部スクリプトで、自動的にピボットテーブルを更新する方法

1. データシートの最終行を #S 記号で削除する方法

ひな形シートの3行目までをピボットテーブルからデータソース参照しているため、余分な空白行（最終行）をカテゴリーにしてしまうので、その行を削除する手法です。

原本ひな形データシートの3行目、再右列の隣に「#S」記号を入れておきます。

#S は、Spacerの意味合いで、処理後に削除する行（分類項目の列位置）に指定します。

データをインポートする制御シートに、#行番号 を設定しておきます。

「#行番号」は、[固定値] を「%ROW」にして行番号を生成し、[区分] を分類項目（この例では「詳細行」）にしておきます。[整形対象部分の右下] を、ひな形データシートの3行目、#S 記号を記入した列のセルにしておきます。

この方法で (空白) カテゴリーはできなくなりますが、各行が分類カテゴリー（#行番号）となるため、データ量が多いときはかなり処理時間がかかります。

2. 外部スクリプトで、自動的にピボットテーブルを更新する方法

この手法を使う場合、原本ピボットテーブルの [データソースの変更] で3行分の範囲指定を前もってしておく必要はありません。 外部スクリプトがデータシートのレコード数を判断して、自動的に [データソースの変更] を行うからです。また同様に、[ファイルを開くときにデータを更新する] をチェックオンにしておく必要もありません。

また、処理速度も 1. の手法よりもかなり速く実行できます。

データインポート手順の下に、外部アプリ実行手順を加え、外部スクリプト（VBScript）を実行してピボットテーブルの更新を行います。

実行するファイル　SCRIPT\PivotUpdate.vbs　（この例では [SCRIPT] フォルダに配置）
　　　　　　　　　⇒ スクリプトのソースコード（現在、このリンクは存在しません。）
コマンドライン引数
　　第1引数　出力ブックのパス名
　　第2引数　データシート名（この例では「データ」）
　　第3引数　ピボットシート名（この例では「ピボット」）
　　第4引数　ピボットテーブル名（この例では「"ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1"」）
作業フォルダ　.　（カレントフォルダを指定）

Designer：ピボット処理の自動実行

次の記事で、Excelでのピボットテーブル、ピボットグラフの作成方法を解説しましたが、ピボットのテンプレートブックを使った、xoBlosでの自動実行処理について記事にします。

ピボットテーブル、ピボットグラフを活用しよう！ http://xoblos.hatenablog.jp/entry/2017/08/13/180832

上記の記事では、28,384レコードのデータを基にピボットテーブルを作成していました。しかし、次回の処理ではレコード件数はもっと少ないかもしれないし、多いかもしれません。その都度、データを入力し直して、ピボットテーブルを更新していくのも面倒なので、xoBlosを利用して、入力したレコード件数分でのピボットテーブル、ピボットグラフを自動実行で作ってみましょう。上記の記事で使ったブックを例に解説します。

ピボット用のテンプレートブック（原本ブック）を用意する。

上記の記事で作成したブックのピボットシートで、[ピボットテーブル ツール]-[オプション]-[データソースの変更] を選びます。

ダイアログの [テーブル/範囲] の最下行数を3行目に変更します。この例では、$J$28385から$J$3に変更しています。この3行目に変更するところが、xoBlosでの重要なポイントとなります。

グラフのタイトルを、自由に変更します（この例では「顧客別・商品区分別販売分析」）。ピボットシート名は、作成時は「Sheet1」のような名前が付きますが、自由に変更します（この例では「ピボット」）。

ピボットテーブルをポイントして、右クリック [ピボットテーブル オプション] を選びます。

[データ] タブで、[ファイルを開くときにデータを更新する] をチェックオンにします。

データシートのデータ行（2行目以下）をクリアします。

ブックを保存します。この例では、[03_TEMPLATE] フォルダに、原本_販売分析データ.xlsx という名前で保存しています。

xoBlos Designerで、自動実行処理を組み立てる。

xoBlos Designerで設定する手順は3手順のみです。

手順１：入力データを抽出してXMLファイルにする。

ExcelファイルやCSVファイルから、データを抽出してXMLファイルにします。制御シートを使用する方法、CSVからXMLへの変換手順を使う方法などがあります。

手順実行でXMLファイルになったことを確認します。

手順２：ピボット用のテンプレートブック（原本ブック）を、出力用フォルダにコピーします。

手順３：コピーした原本ブックに対して、抽出XMLデータをインポートします。

[ひな形シート名] は、データシートのシート名にしてください（この例では「データ」）。高速生成エンジンは必ずオフにしてください。

 フィルタリング操作のオン/オフで、必要なフィールドのみを絞り込んで表示できます。

データ範囲を3行目までに設定した関係で、(空白) カテゴリーが作成されてしまいますが、気になるときは、軸フィールドのフィルタリングで (空白) をオフにしてください。

注記： (空白) カテゴリーをどうしても作成したくない場合は、ピボット作成時に(空白) カテゴリーを作成させない手法がありますので、こちらの記事を参照してください。