Excelへのテキストの貼り付け(ペースト)が途中で切れる時の対処法

表題のように、Excelへテキストを貼りつけると、貼りつけたテキストが途中で見切れてしまうことがあります。
原因は、貼り付けようとしているテキストにNUL文字(16進数の文字コードでx00)が含まれているからです。
NUL文字が含まれていると、そこで貼りつけが終了してしまいます。
(なお、メモ帳への貼り付け、サクラエディタへの貼り付け等でも同じようにNUL文字の箇所で切れます)

NUL文字をスペース(x20)に置換して問題ないのであれば、予めNUL文字をスペースに置換してからコピーすることで、この問題を解決できます。
詳しい手順は以下の通りです。

1.テキストをファイルに出力する

初めからファイル形式なのであれば問題ないのですが、問題なのはDBのレコード等、ファイル形式になっていない場合です。
DBのレコードであれば、CSV等の形式でエクスポートする必要があります。
エクスポートの方法はRDBMSによって異なるので、別途調べる必要があります。
なお、Oracle SQL DeveloperのようなGUI操作ができるツールを使用しているのであれば、ツール上で探せばエクスポート用の機能が見つかると思います。

2.16進数の置換ができるエディタで置換する

1で出力したファイルを、エディタで開き、NUL文字(x00)をスペース(x20)に置換します。
置換の方法は以下の通りです。

・サクラエディタの場合

 以下のように指定して置換すれば良いです。
  置換前:\x{00}
  置換後:\x{20}

・秀丸の場合

 開いた瞬間に自動的にNUL文字がスペースに置換されます。

・Stirlingの場合

 置換機能で、16進データを選択し、以下の指定をすれば良いです。
  置換前:00
  置換後:20

3.置換後のテキストをコピーし、Excelに張り付ける

 これで途切れずに貼りつけができるはずです。


いかがでしたでしょうか。

プログラムで出力したテキストには、NUL文字が含まれることが少なくありません。
NUL文字はC言語系のプログラムでは終端文字として使われているからか、Excelに貼りつけることができず、このような考慮が必要になります。

躓く人も少なくないと思うので、今回記事にしてみました。
何かの助けになれば幸いです。

Excel:ピボットテーブルの使い道と作り方(テストデータ付き)

ピボットテーブルとは、集計作業を行う機能のことです。
自力で集計用の表を作成して関数を書くことでも集計作業は可能ですが、ピボットテーブルを用いればマウス操作だけで簡単に集計作業を行うことができます。

今回の記事では、架空の体力測定結果を集計する例を挙げて、ピボットテーブルの使い方を説明します。
この例では、握力について、男女で差があるのか、測定月で差があるのかを、平均値を出すことで確認します。

なお、使用するデータは以下からダウンロードできます。手を動かしながら覚えたい方はダウンロードしてみてください。
https://cyzennt.co.jp/blog/wp-content/uploads/2021/10/f0baae9405ab2b4cf966538a631c041e.xlsx


【手順】

1.集計作業を行う表をドラッグで選択し、挿入>ピボットテーブルを選択する。

2.ドラッグした範囲が自動的に選択されているので、OKボタンを押下する。

3.ピボットテーブルのシートが生成されるので、列に測定月、行に性別、値に握力(kg)をドラッグして選択する。

4.「合計 / 握力(kg)」の右にある矢印を選択し、値フィールドの設定を選択する。

5.デフォルトでは合計が選択されているので、平均を選択し、OKボタンを押下する。

6.性別ごと、また測定月ごとで、握力(kg)の平均値が算出される。

これで、男女では大きな差があるが、測定月では大きな差がないことが一目でわかるようになりました。


いかがでしたでしょうか。

実務でも集計作業を行うことはあり、例えば性能試験の結果の分析で集計作業が必要になります。
そのような場合に、ピボットテーブルで作業を効率化することができるので、覚えておいて損はない機能だと思います。

ExcelでINSERT文を作成

Excelの機能を使うことで、簡単な手順で大量のSQLのINSERT文を作成することができます。
臨時の運用対応ではデータが公表されてから30分以内にDBに反映させなければならない等、限られた時間でのデータ補正が必要になるため、プログラムを事前に用意できない場合はExcelでの対応が有効になります。
運用対応だけでなく、開発時のテストデータ作成も効率的に行うことができます。

以下で、例を挙げて説明していきます。

【INSERT対象のテーブル定義】

【INSERT文の作成】

①下記のように入力する。

G2セルについては、下記の式を入力する。

=A$2&”000000″

この式によりA2セルと文字列”000000″を結合する。

参照時には$マークをつけて絶対参照とする。

(後の手順でコピーする時に参照先がずれないようにするため)

②B2~H2セルを3~6行目にコピーする。

③C3セルに2を入力してから、C2~C3セルを選択し、4~6行目までオートフィルする。

④B2~H6セルをコピーし、サクラエディタ等のテキストエディタに張り付ける。

⑤セルを区切るtabを削除する。

サクラエディタの場合は、”\t”を””に置換することで削除できる。

【完成したINSERT文】


なお、UPDATE文やDELETE文等も、同じ要領で作成することができます。
また、今回は紹介していませんが、区切り位置、重複の排除、フィルタ等も使用頻度が高い機能であり、これらの機能を使えばフォーマット変換やデータ抽出等も可能になります。


いかがでしたでしょうか。

今回紹介したようなテクニックは技術的には難しいことはしていませんが、実務で良く使う便利なテクニックです。
このようなテクニックはまた紹介していきたいと思います!

Excelで文字の一括置換を行う方法(外部プログラムからの操作)

Excelファイル(.xlsx)は複数のxmlファイルを圧縮して構成されています。
解凍することでxmlファイルの形式として開けるようになり、通常のテキストエディタで編集することが可能になります。
これを利用することで、全てのシートに対して、オブジェクト内(例えば吹き出し内)の文字も含めた文字の一括置換が可能になります。
(Excelの機能では、オブジェクト内の文字を検索したり置換したりすることができません)

以下、手順例です。

1.Excel形式のファイルを用意する

2.拡張子を「.xlsx」から「.zip」へ変更する

3.2のファイルをLhaplus等で解凍する

4.サクラエディタのgrep置換等の機能で文字の一括置換をかける

※本文中の文字は xl\sharedStrings.xml に定義されます。
※図形中の文字は xl\drawings\drawing*.xml に定義されます。
※drawing*.xml に関しては、半角文字と全角文字が入れ替わる箇所で、xmlファイル上で別々の定義になるので注意が必要です。例えば、「hogeほげ」という文字については、xmlファイル上で「hoge」と「ほげ」という文字に分割されます。

5.Lhaplus等で再度圧縮する

※1のファイル名のフォルダの直下で作業を行う必要があることに注意

6.圧縮されたファイルについて、拡張子を「.zip」から「.xlsx」へ変更する

7.6のファイルを開いて置換されたことを確認

※「~の一部の内容に問題が見つかりました。可能な限り内容を回復しますか?」と聞かれるかもしれませんが、「はい」で良いです。


なお、7-Zipを用いれば、解凍や圧縮をコマンドラインから行うことが可能になります。 また、サクラエディタにはコマンドラインからマクロを読みこませる機能もあります。 これらの機能を組み合わせれば、バッチファイルから複数のExcelファイルに対して一括置換を行うことも可能になります。


いかがでしたでしょうか。

このように、xlsxファイルは複数のxmlファイルで構成されているため、解凍・再圧縮を行うことで外部プログラムからの操作が可能になり、作業を効率化できる可能性があります。
少々強引な方法にはなりますが、知っておくと使える場面があるかもしれません。

Excel・VBA:始めてのマクロの作り方(Hello World)

Excelのマクロ(VBA)の作り方の紹介です。
マクロを使える設定にしてから、「Hello World」をポップアップで表示するボタンを作成し、保存する所まで紹介します。
Excelのバージョンは Excel 2013 とします。

手順1:マクロを開発可能にする

Excelの任意のファイルを開き、「ファイル」タブを開く。
「ファイル」タブの「オプション」を選択する。

オプションが表示されるので、「リボンのユーザー設定」タブを選択し、「開発」のチェックボックスをONにし、「OK」を押下する。

手順2:マクロ有効ブックとしてファイルを保存する

「ファイル」タブを開き、「名前を付けて保存」を選択する。
保存する際、ファイルの種類を「Excel マクロ有効ブック(*.xlsm)」を選択する。

手順3:ボタンを作成する

「開発」タブの「挿入」から「ボタン(フォーム コントロール)」を選択する。

任意の場所にボタンを作成すると、「マクロの登録」ウインドウが開く。
ここで「新規作成」を選択する。

ソースコードの入力画面に遷移するため、下記のように記述する。
(ボタンの名前が「ボタン1」の場合)

手順4:ボタンの稼働確認をする

手順3で作成したボタンをクリックする。
「hello world!」と書かれたポップアップが開くことを確認する。


いかがでしたでしょうか。

今回の例はごく平易です。
しかし、ここまでできるようになれば、プログラム言語の経験者であれば自分で調べながら実務レベルのマクロを作ることができるようになります。
VBAはプログラム言語の一つですので一般的なプログラム言語に備えられている機能は一通り揃っていますし、それをセル参照・更新の機能と組み合わせれば、色々な作業の自動化が可能になります。

これからも役に立つ情報を発信していきたいと思います!