【1行Tips】Excel関数：都道府県文字を取得

=IF(ISERR(FIND("県",LEFT(A2,4),1)),
 IF(ISERR(FIND("都",LEFT(A2,3),1)),
 IF(ISERR(FIND("道",LEFT(A2,3),1)),
 IF(ISERR(FIND("府",LEFT(A2,3),1)),"N/A",
 LEFT(A2,FIND("府",A2,1))),
 LEFT(A2,FIND("道",A2,1))),
 LEFT(A2,FIND("都",A2,1))),
 LEFT(A2,FIND("県",A2,1)))

VBAマクロを利用した繰り返し処理

業務でパソコンを利用している以上、エクセルは日常的に使います。

Excelでの印刷関連の設定は複数シートまとめて行う事ができないので
マクロで記録して、ちょこっとVBAを書いて連続処理することで手抜きを実現しています。

手順の概略

１．開発タブ→マクロの記録　で、行いたい印刷設定を行う。
　→開発タブが出ていない場合にはググって調べてください。設定で出せます。
２．以下のテンプレコードをVBAに貼り付ける。
３．テンプレコードを実行する

テンプレートコード（そのまま貼り付けて１行だけ修正）

Public Sub ExecuteAllSheets()
    
    Dim ws As Worksheet
    
    '高速化対応：再描画、都度計算を無効とする
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    'エラーハンドリングの開始
    On Error GoTo ErrorHandler
    
    '全シートに対して繰り返し処理を行う
    For Each ws In ThisWorkbook.Worksheets
        ws.Select
        Call Macro1   '(ここを変更)ここで繰り返し行いたい処理を指定
    Next
    
    'エラーハンドリングをやめてプログラム終了処理へ
    On Error GoTo 0
    GoTo Program_Exit

ErrorHandler:
    MsgBox "エラーが発生しました" & vbCrLf & _
            "No." & Err.Number & vbCrLf & _
            "Message : " & Err.Description, vbExclamation, ""

Program_Exit:

    '高速化対応：解除
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationManual

End Sub

【補足】

・プリンタ関連の処理を記憶すると、自働で作成されたコード中に以下が記録されるケースがあります。
　→ActiveWindow.SelectedSheets.PrintPreview

　これが記録されていると、処理が連続で行われません。（プレビューで止まる）

・余白調整、ヘッダフッタの一括設定等々、画面でできることはすべて処理できます。
　→どこで何ができるかは自動作成されたコードを見て判断してください。

Excel活用術 Tips (INDIRECT,SheetName列挙)

IT関係の仕事に勤めていると、Excelの手軽さって、あまり業務に活用されないのですが、
改めて使ってみると、大変便利なツールだなと、日々感じています。

⇒いわゆる業務システムだと、Excelの柔軟さが仇となったりするので、
　きっちりした業務がお好みのお客様には使えないのです。

ワークブックのシート名を列挙する

○名前の管理にて、以下関数を埋め込み。 (ここでは例として、名前をSheetNamesとします)

=IF(GET.WORKBOOK(4)>=ROW(),MID(INDEX(GET.WORKBOOK(1),ROW()),FIND("]",INDEX(GET.WORKBOOK(1),ROW()))+1,31)&T(NOW()),"")

○どこかのセルに、=SheetNames と記述

これだけで、シート名が出ます。
縦に並べて貼り付けると、シート名一覧の出来上がり。
※ネットで拾った情報の備忘録です。(MARBIN)さん、ありがとうございます。

別シートの参照

=IF(INDIRECT("'参照シート'!A1")="","",INDIRECT("'参照シート'!A1"))

別のシート名を動的に指定して、実装する例です。
これを、先ほどのワークシート名列挙と合わせると、全部とれたりします。

シート名をシングルクォーテーションで括ることを忘れないでください。

上記を組み合わせた使い方

=INDIRECT("'"&C3&"'!AU4")

シート名を動的に参照し、値を表示しています。
シート名列の横に並べれば、動的に全シートの指定値が取得できます。

ちょっと注意事項

関数をあれやこれや活用すると、ものすごく便利なのは確かですが、
エクセルを開いた際、または、初回に関数が評価されるときなどには
たくさんの処理が動き、かたまった状態に見える事も出てきます。

適切に使いたい分だけを使う、時には疎結合 (ブック間のリンクなど )も
意識して使うことも大事になります。

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)

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

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

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

Excelで「テーブル」の設定を変更・削除する方法

Excelのテーブル機能はOffice2007よりついた便利な機能ですが、
たまに、意図しない動作をしたりして、同じテーブル内に行を追加
したにも関わらずテーブルの行として認識されなかったり、
テーブルの前に列を追加すると認識されなかったりと、
困ることがあります。

そんなとき、テーブルの作り直し、編集をすれば良い。という発想になるのですが
メニューを見てもなかなか見つけられなかったので、纏めておきます。

やりたいこと

Excelのテーブルを編集、削除したい

実現方法

1) テーブル内のセルを選択
　2) 「デザイン」タブを選択
　3-1) サイズを変えたい場合、「テーブルのサイズ変更」
　3-2) テーブルを削除したい場合、「範囲に変換」


これで、使える機能である「テーブル」が、さらに使えるものになりそうです。

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))

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

ExcelでASCII文字からASCIIコードを取得する(VBA関数)

文字列の先頭文字に対応する ASCIIまたはJISコードを返す
=CODE(文字列)

指定した数値をASCIIコードとみなして対応文字を返す
=CHAR(数値)