Excelの関数実用例

最近、Excelを使ったデータの編集作業を支援していたりするのですが、
一部では「最強のフロントエンドツール」なんていわれるくらいのExcelですので、
いろんなことができる事に改めて気づかされました。

今回は、その際に活用したExcelの実用例などを載せておきます。

●[E2]の値が変更対象シートに該当するデータであれば、"0"、
　そうでない場合には台帳よりデータを取得して結果を表示する
　(VLOOKUP)

=IF(E2="","",IF(ISNA(VLOOKUP(E2,変更対象シート!C$8:C$375,1,FALSE))=FALSE,"0",VLOOKUP(E2,台帳'!$B$13:$D$9587,3,FALSE)))

・VLOOKUP関数は、あいまい検索を許容しない事をお勧めします。

・この例では参照先の指定が絶対値ですが、きっちりやるならテーブル化をお勧めします。

●列数が異なる複数のシート間で、値参照を利用してデータを表示する

　1つのデータが、複数行にまたがる。なんてことはあると思います。

　システム観点では1データは1行である必要がある。

　そんなときにもExcelではよきに計らってくれる方法があります。

　詳しくまとめようとするとそれだけで1記事になってしまいそうなので

　キーワードだけまとめ。

　OFFSET , ROW()

　この関数を使う事で、対処できます。

=OFFSET(参照先シートのデータ参照起点(絶対値),(ROW()-このシートの開始行位置)*このシートのデータ1行あたりのExcel行数,参照起点からの列移動数)

OFFSETを活用すると、複数行のデータを1行に纏める。ってことも関数だけで実現できます。

●データ参照の基本

　=IF(A2="","",A2))

　完璧な処置ではないですが、これを入れるか入れないかで参照の完成度が違います。

　⇒入れないと、ゼロがセットされてしまう事が多いですね。

　完璧ではない。といったのは、条件として=""としてますが、これが、

　実体はEmptyだったりすること、実体Emptyだったとしたときに""をセットしてしまうこと。

　この両方が対応できてないので「完璧ではない」

　そこまで対応すると計算式が増え、ファイルサイズも増え、自動計算も遅くなり、と

　色々でメリットも出てくるので、殆どのケースではこれでいいのではと思います。

●参照されるデータの基本

　 Excelでは、「数値」と「文字」は別物なので、見た目が一緒に見えるのに

　 VLOOKUPで比較しても一致しない事があります。

　 そんなとき活用するのがTRIM

　 =TRIM(A2)

　 とするだけで、文字認識されます。

まとまりきらないのでこのへんで。

まだ随時公開していきたいと思います。

Outlook2010で添付ファイルのプレビューをしないようにする方法

Outlookの添付ファイルを操作している時、開けるファイルだったりすると
自動的にプレビューが表示されてしまいます。

セキュリティ上よろしくないという理由もありますが、
ファイルサイズが大きいと動作上もたつくため、オフにすることにしました。

手順

1) Outlookを開く
2) 「ファイル」→「オプション」を選択
3) 「セキュリティセンター」タブを選択し、「セキュリティセンターの設定」ボタンを押す
4) 「添付ファイルの取り扱い」タブを選択し、「添付ファイルのプレビューをオフにする」にチェック

Outlookを再起動すると設定が反映されます。

GoogleChromeを起動する際の起動オプションを指定する

Chromeには、起動に関するオプションが用意されています。

ショートカットにオプションをプラスする事により、起動オプションを付与した形で
Chromeを起動することができます。

やりかた

・ショートカットを右クリックして、プロパティを選択
・「リンク先」の、末尾に、起動オプションを付与して「OK」にて保存
・ショートカットから起動

実施例

・指定したページだけを開く
　"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" --no-startup-window "http://www/"

・マルチアカウントの際、プロファイルを指定して起動
　--profile-directory="Default"
　※プロファイル名は、chromeより、chrome://versionと入力すると表示されます。
　※プロファイルのデータは、「　%USERPROFILE%\AppData\Local\Google\Chrome\User Data\　」に保管されています。(Win8.1で確認)

・ユーザーエージェントをスマートフォンとして起動
　--user-agent="Mozilla/5.0 (Linux; Android 4.0.3; ISW12HT Build/IML74K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.59 Mobile Safari/537.36"
　※サイトにより、スマートフォン認識されない場合もありますので、完全ではありません。

Excelで保護されたセルに色を付ける

Excelでセルの保護を使うことがありますが、あちこちに設定していると
ぱっと見で確認したいケースが出てきます。

1行Tipsですが、忘れた時のためにとっておきます。

手順

1) 条件付き書式を使用します。
2) 「数式を使用して、書式設定するセルを決定」を使用します。
3) 条件に、以下を入力し、書式で背景色などを設定します。
　=cell("protect",a1)=1
　※開始位置が「a1」でない場合には適宜書き換えてください。

なんだかんだ言ってExcelって最強のフロントエンドツールだったりすることを最近つくづく感じます。

Excelで単一行と複数行のシートをセル参照で解決する

イメージ的には以下のような感じです。

元々1行のものを、複数行の表で参照しようとした場合、

「=表!C3」　というような表現だと、単純に参照設定を作ることができません。

1行が1行として参照するのであれば、一つ参照をつくって、コピーすれば済みますが、

複数の場合、どうやっても単純コピーではいきません。

そこで、調べてみたところ、別アプローチでの解決方法がありました。

キーワード

OFFSET　・・・　指定した参照から指定した行数、列数の範囲への参照を返します。

ROW()　　・・・   参照の行番号を返します

COLUMN()　・・・　参照の列番号を返します。

理屈

OFFSETで、相対位置を指定する事により、任意の場所の値が取れます。

ROW()とCOLUMNを利用して、スタート位置からの相対を指定します。

実践

別シートに分かれているものとします。

2行構成のシートから、1行校正のシートを参照します。

・行Noを取得する (行Noは、A列2行目に1行目がある)

　=OFFSET(表!$A$2,((ROW())-2)/2,0)

　※解説

　　・表A2からスタートで、相対位置(行)を計算、列はそのまま。

　　・1行目の相対位置は、$2から見るとゼロ。ROW()-2/2もゼロ。よってそのままの行

　　・2行目はとなると、ROW()が4なので、(4-2)/2 でイチ。$A$2から行がプラスイチなので、3

相対位置のスタートを絶対指定にするのがポイントです。

これで、同じ計算式を使いまわすことが可能です。

ちなみに、名称の欄には

　=OFFSET(表!$B$2,((ROW())-2)/2,0)

と指定しています。

きちんと使うなら、

=IF(OFFSET(表!$A$2,((ROW())-2)/2,0)="","",OFFSET(表!$A$2,((ROW())-2)/2,0))

とやると、きれいにいくかもしれません。

Windows8.1で、ファイル右クリックで「印刷」が出ない。(Adobe Reader)

気のせいかもしれませんが、昔はファイルを右クリックすると
メニューに「印刷」が出てきた気がするのです。

今は出てきません。

数十個のファイルを一つづつ開いて印刷など、気が遠くなりそうなので
やり方を調べてみたところ、出てきました。

プログラムを起動するときに、パラメータを指定すれば良いです。

やりかた

以下を、コマンドプロンプトへ貼り付けてください。

1) ディレクトリを移動

cd /d 対象のディレクトリ

2) Adobe Readerのパスを変数にセット　※適時変更してください

SET EX="C:\Program Files (x86)\Adobe\Reader 10.0\Reader\AcroRd32.exe"

3) 存在するファイル分、繰り返しで印刷処理を実行

for %i in (*.pdf) do %EX% /t %i

通常使うプリンタへ出力されます。

追記：

○連続実行で不具合が出る場合、ダミー的に遅延を作ることができます。

　for %i in (*.pdf) do %EX% /t %i | ping 127.0.0.1 -n 10

○Win8.1 + AcroRead10の場合、印刷完了後にAcrobatReaderが終了しませんでした。
　Win7では動作したので今後検証していきたいと思います

---------------------------------------------------------------------

Adobeのサポートサイトより引用

http://helpx.adobe.com/jp/acrobat/kb/510705.html

- AcroRd32.exe filename

Adobe Reader で PDF ファイルを表示する

起動オプション
/n ： すでに Acrobat を起動している時に、別のインスタンスとして Acrobat アプリケーションを起動
/s ： スプラッシュ画面を表示せずに、Acrobat を起動
/o ： [ファイルを開く] ダイアログを表示せずに、Acrobat を起動
/h ： Acrobat を起動するが、表示しない（メモリ上にロードするのみ）

- AcroRd32.exe /p filename

Adobe Reader で PDF ファイルを印刷する

- AcroRd32.exe /t path printername drivername portname

Adobe Reader を起動し、印刷ダイアログボックスを表示せずに PDF ファイルを印刷し、Adobe Reader を終了する

印刷オプション

path ： ファイルパス
printername ： プリンタ名
drivername ： プリンタドライバ名（プリンタのプロパティで確認）
portname ： プリンタポート名（"/"という文字を含まないようにしてください）

隠れてしまったWindowを移動する方法

たまに、やらかしてしまいすぐ忘れてしまうので備忘録として残しておきます。

Windowsのアプリケーションの中には、位置を記憶していて、次回起動するときに

その位置で開いてくれるものも多いです。

複数のディスプレイを使っていると、状況によりディスプレイの位置が変わってしまったりして、

そうなると、行方不明ということになってしまいます。

イメージ

対処法

1. タスクバーで該当のアプリケーションを1回クリックする　⇒フォーカスがある状態になります
2. キーボードの[Alt]キーを押しながら、[Space]キーを押します。
3. 続けて、[M]キーを押します。
4. キーボードの矢印キーを、１回押します。
5. マウスを、動かすと、画面が一緒に動いてきます。
6. 場所がよければ、マウスでどこでもよいのでクリックします。

複数ディスプレイの環境だけでなく、大きい画面から小さい画面に変わった時なども有効です

日本郵政の郵便番号データをまとめて使う方法

システム開発屋であれば、運用方法等踏まえた上で
最適な提案をするのが筋ですが、とりあえず簡単にやるときに
使えそうな手順を

1.日本郵政のサイトより、郵便番号データをダウンロード

http://www.post.japanpost.jp/zipcode/dl/kogaki.html

2.ダウンロードしたデータを展開して、同じフォルダに格納。

3.コマンドプロンプトを立ち上げて、上記フォルダへ移動後、以下コマンドを実行

 del list.csv
 dir /b *.csv > list.txt
 del result.csv
 ECHO "全国地方公共団体コード","旧郵便番号","郵便番号","都道府県名カナ","市区町村名カナ","町域名カナ","都道府県名","市区町村名","町域名","一町域複数郵便番号区分","小字毎番地起番区分","丁目有町域区分","郵便番号複数町域区分","更新表示","変更理由" > result.csv
 for /F %i in (list.txt) do copy result.csv + %i result.csv

 これで、result.csvというデータに、全国のデータが入りました。

 あとは、取り込んで使うなど、好きに加工