Studio ODIN

Excel で 手軽に2018年（前年）の日付を入力する

（使用している Excel は Excel 2016 です）

この時期、Excel で前年の日付を入力するケースは 結構多いと思いますが、 Excel では 年を省略すると PC の年を使用するようで "2019/12/28" のようになってしまいます。

普段は便利なこの省略も この時期はとても困ります。 ソート順や計算にも影響しますし そもそも正しい値じゃないです。

Excel に標準で対応する機能はないようで VBAも・・・と思っていたら 良い方法が紹介されていました。

[参考]
来年の日付入力を簡単に?オートコレクト:Excel(エクセル)の使い方-入力・編集

このサイトの方も 別の記事を紹介しているようですが 画像付きで分かりやすく説明されています。

オートコレクトで 特定の文字を "2018/" のよう 固定で前年の値に変換させるわけですが 泥臭いようでこれが実に便利です。 シートごとに設定しなくても良いのも良いですね。

日付を入力することが多いと かなり役立ちます。

Excel の覚えておきたいショートカット 10選 2018年版

（使用している Excel は Excel 2016 です）

ファンクションキー系

[F4]

やっぱり、これ [F4]。

前の処理を繰り返すことができるので、同じ操作を続けるときに超便利。 それ以外でも、数式の入力時にセルの参照の絶対・相対を切り替えてくれるので 使用頻度が高いキーです。

[F2]

セルの選択から編集モードに入るときにも使用しますが 入力モードから編集モードに切り替えるときも使用します。

書式系

[Ctrl] + [Shift] + [~]

セルの書式の「分類」を「標準」に戻すショートカットです。
詳しくは Excel で セルの書式の「分類」を「標準」に戻すショートカット を参照。

値の入力系

[Ctrl] + [;] (セミコロン)
[Ctrl] + [:] (コロン)

セミコロンの方は、セルに現在の「日付」を入力します。 コロンの方は「時刻」です。 今日の日付を入力する機会は多いので よく使います。

[Alt] + [Shift] + [=]

選択した範囲に対して SUM ワークシート関数を入力します。 リボンにある Σ ボタンと同じですが ちょっと検算するときにマウスを使わなくても済みます。

[Ctrl] + [d]
[Ctrl] + [r]

枠線系

[Ctrl] + [Shift] + [&]

選択範囲に 外枠を引くショートカットです。
詳しくは Excel の 罫線に関するショートカット 2つ を参照。

[Ctrl] + [Shift] + [_]

選択範囲内の罫線を消すショートカットです。
詳しくは Excel の 罫線に関するショートカット 2つ を参照。

ダイアログ表示系

[Ctrl] + [1]

「セルの書式設定」ダイアログを表示します。

[Ctrl] + [Alt] + [v]

セルを複写中に押すと「形式を選択して貼り付け」ダイアログを表示します。 とても押しにくいキーなので 使っていると 「Excelがメッチャ好きな人だ」と思ってもらえます。

Excel で 月末日を取得する EOMONTH

（使用している Excel は Excel 2016 です）

各言語や SQLなどで 「月末」を取得する関数がありますが Excelのワークシート関数にもあります。

EOMONTH(開始日,月)

EOMONTH という関数で 日付と何か月後（前）を指定すると 月末の日付が返ってきます。

「今日」と「0ヵ月」を指定すると 今月の月末日になります。

=EOMONTH(TODAY(),0)

Excel では日付に +1 すると その日付の翌日になるため、 次のようにすると翌月の初日になります。

=EOMONTH(TODAY(),0)+1

他にも色々使えます。

月末日なので DAY 関数で その月の日数を取得できます。

=DAY(EOMONTH(TODAY(),0))

DAYS 関数なら 今日から月末日までの日数を取得できます。

=DAYS(EOMONTH(TODAY(),0),TODAY())

Excel で 数値を K (キロ) や M (メガ) で表示する

（使用している Excel は Excel 2016 です）

会社で扱う資料などでは 金額が大きいため 「〇千円」や「〇Ｋ円」のように 桁を減らして記載することがあります。

"K" は "Kg" の "K" で 1000倍、 "M" は 100万倍です。

今回は、これをユーザー定義書式で実現する方法です。

このような書式の確認では TEXT ワークシート関数を使うのが わかりやすくて良いです。

TEXT(値,表示形式)

今回の話からはそれますが TEXT ワークシート関数は文中の値を整形したいときに 使える便利な関数です。

="今日は" & TEXT(TODAY(),"mm月dd日") & "です"

まずは普通の数値書式。

普通ですね。

数値を 3桁上げるには 数値書式の最後にカンマを付けます。

#,##0,

1000未満の 3桁が消えました。

自動的に四捨五入もされます。

この書式に "K円" などを付けます。

#,##0, "K円"

"M円" で 6桁上げる場合はカンマが 2つです。

#,##0,, "M円"

100万単位だと大き過ぎるときは 小数表記も併用できます。

#,##0.0,, "M円"

Excel の 罫線に関するショートカット 2つ

（使用している Excel は Excel 2016 です）

今回は Excel の罫線に関するショートカットを 2つ紹介します。 良く使うものがショートカットになっています。

[Ctrl] + [Shift] + [&]

まず、こちら。

[Ctrl] + [Shift] + [&]

選択範囲に 外枠を引くショートカットです。

外枠を引くだけで 範囲内の罫線を消したりはしません。

これは「罫線」のメニューにある「外枠」と同じ動きです。

[Ctrl] + [Shift] + [_]

次に、こちら。

[Ctrl] + [Shift] + [_]

選択範囲内の罫線を消すショートカットです。

不要な罫線をまとめて消せるので便利です。

Excel の ユーザー定義書式�B

（使用している Excel は Excel 2016 です）

前回の「Excel の ユーザー定義書式�A」の続きです。

Excel の ユーザー定義書式の記号に "*" があります。

*

"*" は、「 "*" の後ろの1文字のを繰り返す」という記号なのですが 色々と面白い使い方ができます。

まずは、右寄せ・左寄せです。

* @

@*  (@の後ろに半角スペース）

このようになります。

わかりにくいので 半角スペースを "_" に変えてみます。

*_@

@*_

この "_" は、書式なので テキストエディタなどにコピペすると値の "abc" のみになります。

次に左右分割です。

「様」などの敬称を付ける場合に使えます。

@*_様 （わかりやすさのために "_" にしています）

このように名前と敬称を左右にわけることができます。

他にもコメントの飾り文字を作ったり、、、

+++ @ *+

セルの幅に関係なく文字で埋めたりできます。

*+

（空だと表示されないため "0" などの値を入れておきます）

Excel の ユーザー定義書式�A

（使用している Excel は Excel 2016 です）

前回の「Excel の ユーザー定義書式�@」の続きです。

セミコロンは 書式の区切り記号ですが セミコロン自体を表示したい場合は エスケープする必要があります。

Excel の ユーザ定義書式のエスケープ記号は "!" です。

!

セミコロン以外の記号でも同じです。

たとえば 文字の最後に ";" を付ける場合、、、

@!;

このようになります。

エスケープ記号 "!" を表示する場合は "!!" にします。

!!

Excel の ユーザー定義書式�@

（使用している Excel は Excel 2016 です）

前に セミコロン 3つのユーザー定義書式でセルの値を非表示にする というネタを書いたのですが 今回は これをもうちょっと掘り下げてみたいと思います。

ユーザー定義書式のセミコロンは 書式の区切り記号です。

つまりセミコロン 3つは 書式の区切りしかないため、 「書式が無い」という設定になります。

;;;

「書式が無い」ため 値が非表示になるわけです。

このユーザー定義書式ですが 3つのセミコロンで 4つの定義に分けられます。

それぞれは 次の設定になります。

�@正の数の書式
�A負の数の書式
�Bゼロの書式
�C文字の書式

例えば、次のように「�Bゼロの書式」だけ設定すると

;;ゼロ;

このようにゼロのときにだけ表示されるようになります。 （この書式では "ゼロ" という文字を設定しています）

セミコロンを 2つにすると 「�@正の数の書式」「�A負の数の書式」「�Bゼロの書式」を「書式なし」で設定したことになり 「�C文字の書式」は未設定になります。

;;

未設定の「�C文字の書式」は、標準の書式になります。

これを踏まえると 次のような「負のみ表示」する書式や

;-#,##0;;

ゼロのときに文字を赤くする書式を設定することができます。

#,##0;-#,##0;[赤]0;@

この [赤] は、 文字を赤色にする記号で "@" は文字をそのまま表示する記号です。

色は以下の記号が定義されています。

[黒][白][赤][緑][青][黄][紫][水]

定義されていない色も数字で指定できます。

[色1]〜[色56] （[色1]が[黒]、[色8]が[水]）

正負くらいであれば 「条件付き書式」を使用しなくても 目立たせることができます。

[緑]#,##0;[青]-##0;[赤]0;[紫]@

Excel や Word から一括で画像を抽出する

（使用している Excel, Word は Excel, Word 2016 です）

Excel から簡単に画像を抽出するネタです。

Excel 2007 以降は ".xlsx" という拡張子で保存できますが これは ZIP ファイルとして解凍することができます。

それを利用した方法です。

次のように画像が貼り付けてあるシートがあります。

ファイルの拡張子を ".xlsx" を ".zip" に変更して解凍します。 （".xls" の場合は、".xlsx" で保存しなおしてください）

解凍すると、 画像がフォルダにまとめられています。

画像が多かったり 複数のシートに貼り付けてあるときなど 役に立ちます。

Word も同じように 拡張子 ".docx" を ".zip" に変更して解凍すれば 画像を簡単に抽出することができます。

Excel で 「改行」を検索・置換する

（使用している Excel は Excel 2016 です）

Excel で「改行」を削除したり 逆に「空白」を「改行」に置換したいときがあります。

セルの中であれば [Alt] + [Enter] で改行させることができますが 「検索と置換」ダイアログの中では [Alt] + [Enter] では改行しません。

セル以外の場所で「改行」を入力するには 以前、ここで 書いたように [Ctrl] + [J] を使用します。

なぜ "J" なのかについては 次のサイトに書いてありました。

[参考]
秘密の改行キー「Ctrl」＋「J」（第24回） | 日経 xTECH（クロステック）

Excel で セルの書式の「分類」を「標準」に戻すショートカット

（使用している Excel は Excel 2016 です）

セルの書式の「分類」を「標準」に戻すショートカットです。

勝手に日付型になってしまったセルや 文字列型のセルの数式を有効にしたいときに キーボードだけで操作でき便利です。

ショートカットは次のようになります。

[Ctrl] + [Shift] + [~]

あくまで 書式の「分類」を「標準」に戻すだけなので 文字のフォントや色、 セルの背景色などは そのままです。 Word の [Ctrl] + [Shift] + [n] のように 書式をクリアするわけではありません。

Excel の VLOOKUP では検索条件にワイルドカードが使える

（使用している Excel は Excel 2016 です）

VLOOKUP ワークシート関数は 検索条件にワイルドカードが使える というか 使えてしまいます。

次のように ワイルドカードの "*" が含まれている場合、、、

=VLOOKUP("*大久保",C2:D3,2,FALSE)

"新大久保" なんかが先にあると、完全一致なのに こちらがヒットしてしまいます。

この場合、次のように ワイルドカードの "*" を "~" で エスケープする必要があります。

=VLOOKUP("~*大久保",C2:D3,2,FALSE)

"?" も同様に "~" でエスケープします。

=VLOOKUP("~?大久保",C2:D3,2,FALSE)

"~" 自体を使いたいときも "~" でエスケープします。

=VLOOKUP("~~大久保",C2:D3,2,FALSE)

VLOOKUP ワークシート関数で 検索条件にワイルドカードを使うことは そんなに無いんじゃないかと思いますが、 逆に 意図せず含まれている場合は エスケープしてやる必要があるため 注意が必要です。

Excel で セルの値の非表示にする

（使用している Excel は Excel 2016 です）

Excel で 「セルの値を非表示にする」といえば、文字の色を白にする方法があります。

しかし、当然ながらこの方法では 背景の色を変えてしまうと 値が見えてしまいます。

次のユーザー定義書式を使用することで、 セルの値を非表示にすることができます。

;;; (セミコロン3つ)

これで 値が表示されなくなります。

もちろん 値が表示されていないだけですので 数式から利用することはできます。

文字の色を変える方法と違って セルをテキストエディタにコピーしたときは空文字でコピーできたり 列幅の自動調整で邪魔しない（文字の色を変えるだけの場合は列幅の自動調整に影響する）など 他にも良い面があります。

Excel のシートの VeryHidden 設定

（使用している Excel は Excel 2016 です）

Excel の非表示シートにデータを格納して 他のシートから数式で参照する、なんてことはよくありますが その非表示のシートを簡単には表示されたくないときの方法です。

「ブックの保護」で、シート構成を変更不可にすることでも シートを再表示できなくすることはできます。

ただ、この場合は シートの追加やシート名の変更もできなくなってしまいます。

そこで、 Excel の VBA 側から シートの設定を変更します。

「コードの表示 (V)」をクリック。

VBA の画面が表示されます。

ここで、非表示になっているシート「Sheet2」の「プロパティ」の「Visible」属性を見てみます。

「Visible」属性には "0 - xlSheetHidden" が設定されています。 これが通常の Excel 側で設定した「非表示」状態です。

この「Visible」属性には 「表示」状態である "-1 - xlSheetVisible" 以外に "2 - xlSheetVeryHidden" という いかにも強そうな設定があります。

"2 - xlSheetVeryHidden" を設定してみます。

すると、Excel のシート側では再表示できなくなりました。

もちろん「通常の非表示」のシートが他にある場合は、 そのシートの再表示は可能です。

絶対に表示させない、というわけではないですが 他のシートの運用と分けることができますので便利です。

Excel のユーザー定義書式にある「G/標準」も書式

（使用している Excel は Excel 2016 です）

Excel のセルの書式設定の分類「ユーザー定義」は 初期値に「G/標準」と表示されます。

これも書式のようで、次のよう設定して、、、

G/標準年寝太郎

数字を入力してみます。

次のようになります。

「G/標準年寝太郎」の "G/標準" の部分が "3" で、"年寝太郎" の部分が文字列として連結されたようです。

文字列を入力した場合はそのままですね。

"G/標準" ではなく "標準" でも同じようで、 次のように設定すると、、、

標準デス

このようになります。

"G" も含めて「なんだろう？」と思っていましたが、 次のサイトに書いてありました。

[参考]
Excel（エクセル）の表示形式で見かける「G/標準」とは何の意味？ | Prau（プラウ）Office学習所

"G/標準" というのは、Excel の初期状態の、 自動的に四捨五入や指数表示をしてくれる書式とのことです。

つまり、"G/標準年寝太郎" のように設定するのは 「Excel の初期状態の書式」に「文字列の"年寝太郎」を連結させる、という意味になるようです。

Excel で英数字の全角・半角を変換する

Excel には 英数字（＋カタカナ）の 全角・半角を変換する ワークシート関数があります。
（Excel 2010 で確認）

全角⇒半角は「ASC」です。 プログラム言語だとアスキーコードとか返す同名の関数が あったりするので少し紛らわしいですね。

=ASC(文字列)

全角の英数カナ文字（2バイト）を、半角の英数カナ文字（１バイト）に
変換します。

半角⇒全角は「JIS」です。

=JIS(文字列)

半角の英数カナ文字（１バイト）を、全角の英数カナ文字（2バイト）に
変換します。

この 2つの関数はペアなので 変換⇒再変換で元に戻ります。

Excel の翻訳機能

セルをクリックしたら 偶然表示されて 驚いたのですが Excel には 2007 から翻訳機能があるそうです。

[参考]
【TIPS】Excelの翻訳機能を辞書代わりに使う方法 | Excelを制する者は人生を制す　〜No Excel No Life〜

例えば 次のように 英単語の入った セルを [Alt] を 押しながら マウスでクリックします。

「リサーチ」パネルに 翻訳結果が表示されます。

日本語から英語もできます。

「校閲」タブの「翻訳」から表示させることもできます。

翻訳機能の精度は よくわかりませんが こんな機能まであるとは すごいですね。

Excel や Word で選択しにくいオブジェクトを簡単に選択する

Excel や Word で 背景が透明なオブジェクトを複数重ねてしまうと 下になったオブジェクトが選択しにくいことがあります。

こういうときは 「検索と選択」の中に「オブジェクトの選択と表示」というメニューがあるので それを使います。 （Excel 2010 で確認）

図形の一覧なんかが出てきますので・・・。

リストから簡単に選択できます。

Word にも同様の機能があります。 （Word 2010 で確認）

イライラしそうな操作には ちゃんと機能が用意されてますね。

Excel で 白黒印刷プレビュー

パソコンで資料を作成したときに 印刷イメージを確認しておくのは 大切なマナーだと思います。 特に Excel だとセルの中で文字列が切れてたり 表がはみ出してたりしますよね。

さらにカラーだとプリント代が高くなるため 会議には白黒印刷で・・・なんてことも多いのではないでしょうか？

Excel には印刷プレビューを白黒にする機能があります。

上図のように、「ページ設定」⇒「シート」に設定があります。

Excel で書式だけをクリアする

たまに忘れるのでメモ。

Excel で書式だけをクリアするには、 リボンのホームタブにある「クリア」の下に 「書式のクリア」を選択します。

Excel で数式を表示してくれるショートカット

Excel で セルの中に数式が なんだったかな？ どこを参照してるかな？ と思うことがあります。

セルを選択すれば 数式バーに 表示させることができますが 表示を切り替える便利なショートカットがあります。

[Ctrl] + [Shift] + [@]

このショートカットで 選択しているシートの数式が セルに表示されます。 もう1度ショートカットを入力すると 元に戻ります。

ちなみにこれをメニューから 変更しようとすると Excel2010 の場合 次のように 「オプション」→「詳細設定」を開いて 設定する必要があります。

値を入力してくれる便利なショートカットも良いですが 設定を変更してくれるショートカットだと メニューを開かなくて良いので覚えておくと 作業が捗ります。

Excel で、空白のセルにだけ色を塗る

条件付き書式とかではなく、現在空白のセルを 選択して色を塗る方法です。

今回の方法は、 ジャンプ機能を使用しているので 同じ方法で色々応用が効きます。

まず、次のように空白を含んだ範囲があるとします。

対象の範囲を選択します。

[Ctrl]+[g] を押して ジャンプ機能を呼び出します。

表示されたウィンドウの「セル選択」ボタンを押します。

選択オプションが表示されます。

「空白セル」にチェックを付けて「OK」ボタンを押します。

次のように 選択した範囲の中の空白が 選択状態になります。

あとは、この状態で色を設定するだけです。

パッと目で確認したいときなど簡単で便利です。

Excel で範囲の入力中のカーソルキー移動

Excel で範囲の入力中に、カーソル移動して 数字だけを変えたいときがあります。

そのままカーソルキーを押すと下のように 別のセルを選択してしまい 入力欄の中を移動することができません。

ここは、通常のセルの入力と同じように [F2] キーを押すと 編集モードになります。

これでカーソル移動ができるようになります。

Excel や Word 2010 で「開発」タブを表示

デフォルトでは非表示になっている「開発」タブですが 2010 では表示の設定が 2007 と変わってしまっています。

探したのでメモ。

2007 (Excel) の場合は↓です。

「Officeマーク」タブ→「Excel のオプション」→「基本設定」
　→「Excel の使用に関する基本オプション」

2010 (Word) の場合は↓です。

「ファイル」タブ→「Word のオプション」→「リボンのユーザ設定」

個別の設定ではなく、 他の機能と同じように リボンの表示設定の中に入りました。

こちらの方が良いような気がしますが コロコロ変わると ちょっと困りますね。

VBA で使える便利な Like 演算子

VBA(VB) には文字列を比較する Like という演算子があります。 これを使用すると簡単なパターン検索なら正規表現や文字列関数を使用せずに 済ませることができます。

If "abc" Like "a??" Then ... ' ←True

[参考サイト]
Excel(エクセル)VBA入門：Findメソッド,Like演算子を使った検索

# で数字とマッチさせることができますが 全半角問わないので、厳密に半角数字をチェックする時は 次のように [] を使用します。

If "3" Like "#" Then ... ' ←True
If "４" Like "#" Then ... ' ←これも True になる

If "3" Like "[0-9]" Then ... ' ←True
If "４" Like "[0-9]" Then ... ' ←これは False になる

ワークシート関数 SUBTOTAL を使いこなす 1

Excel には SUBTOTAL という便利な関数があります。

基本的な使い方は SUM 関数や COUNT 関数と 同じように範囲を指定して使います。

違うのは上のように「集計方法」を指定することです。

SUBTOTAL 関数は 範囲に対して次のような 「集計方法」に指定で集計ができます。 （上の図の 9 は SUM になります）

引数で集計方法を指定できるのが SUBTOTAL 関数のスゴい使い方ではありません。 （もちろんそれもありますが）

SUBTOTAL 関数は SUBTOTAL 関数の結果を集計に含めない、という 他の関数にはないスゴい特徴があります。

次のように赤色と青色の範囲の小計を出すときに SUM 関数だと範囲の指定が面倒ですが SUBTOTAL 関数は範囲が被っても問題ないので 範囲の指定が簡単になります。

上の図の B5 セルの 6 は SUBTOTAL 関数の結果なので B9 セルの 21 には含まれないわけです。

次回に続きます。

ワークシート関数 SUMPRODUCT を使いこなす 3

SUMPRODUCT 編 最終回です。

次の表のように計算式だけでなく 条件も含めたい場合があります。 これも SUMPRODUCT で対応できてしまいます。

次の表のように計算式だけでなく 条件も含めたい場合があります。 これも SUMPRODUCT で対応できてしまいます。

次のような式になります。

上の表の「購入：○」を入れれば、動的に合計を計算してくれます。

3 つ目の引数がポイントです。
展開すると次のようになります。

  30 * 2 * ( ""  = "○")*1
+ 40 * 1 * ("○" = "○")*1
+ 80 * 4 * ("○" = "○")*1
+ 20 * 3 * ( ""  = "○")*1

ここで「 ( "" = "○") 」の部分は TRUE/FALSE を返すのですが Excel では TRUE = 1, FALSE = 0 という扱いになります。

ただ TRUE/FALSE のままではマズいので 1 を掛けて数値に変換しています。 この方法で、条件式を含むことができます。

計算用の列を持てば同じことができますし、式もシンプルにはなるのですが 計算用の列は、行を追加するときにちゃんと入れてくれなかったり 自分だけが使うのではないときに気を使うことがあります。

SUMPRODUCT だと式は複雑になりますが シートのセルはシンプルにすることができます。

VLOOKUP や INDIRECT ほど主演な感じではないですが 助演くらいはやってくれる便利な SUMPRODUCT です。

ワークシート関数 SUMPRODUCT を使いこなす 2

2 回目です。

前回は次のような 2 列の表の合計を計算しました。

SUMPRODUCT の引数に範囲を 2 つ指定していますが 実は範囲は 30 個まで指定することができます。

ですので、例えば次のような 3 列の表も。

列同士が掛け算なら SUMPRODUCT で計算することができます。

ただ、よく見ると上の表の「消費税」は 5% や 0% なので そのまま「単価」「数量」と掛けると 次のように、おかしなことになってしまいます。

これは当然次のような計算になっているためです。

(50 * 2 * 0.05) + (80 * 1 * 0.05) + (220 * 4 * 0) = 9

ここで消費税を 105% にしたり 税金の列を別に持たせてしまっては SUMPRODUCT を使う意味がありません。

こういうときは次のようにします。

消費税の範囲に「 +1 」をしています。

これは次のような計算になります。

(50 * 2 * (0.05+1)) + (80 * 1 * (0.05+1) + (220 * 4 * (0+1)) = 1609

慣れるまではややこしく感じるかもしれません。

あと 1 回続きます。

ワークシート関数 SUMPRODUCT を使いこなす 1

Excel には SUMPRODUCT という便利な関数があります。

例えば次のような表があります。

この表全体の合計金額を出したい場合 次のように 各行の「単価」と「数量」を掛けて 行の「金額」を計算して それを合計します。

これだとシート上に「金額」欄を 作る必要があります。

次のように SUMPRODUCT を使うと 簡単に合計が出せます。

SUMPRODUCT の引数には「範囲」を指定します。

上の図では、色分けしてありますが 範囲に名前を付けると 次のようにもっとスッキリわかりやすくなります。

SUMPRODUCT は 指定した範囲の対応する要素を掛けて その合計を計算してくれます。

次回は 応用編です。

Excel で外部 Web データの取り込み

Excel には外部データの取り込み機能がありますが これを使うと Web のデータも簡単に Excel に取り込むことができます。

便利なのは、単に HTML としてデータを取り込むのではなく テーブル（表）になっている場合、そのテーブルを指定すると テーブルのデータだけを取り込むことができます。

メニューから「データ」→「外部データの取り込み」→「新しい Web クエリ」を選択します。

取り込む Web ページのアドレスを入力して「移動」ボタンを押すと そのページが表示されます。

上の図の左上に [→] という黄色いマークが表示されていますが これを選択すると「ページ全体」を取り込む指定になります。

次の図のようにページ内のテーブルにも [→] マークが表示されます。 このマークをクリックで選択してみます。

[→] マークが緑色になります。
複数のテーブルを選択することもできます。

取り込むテーブルを選択したら「取り込み」ボタンを押します。

インポート先を指定します。

次の図のように取り込まれます。

1 回取り込むと、その指定を記録するので、「更新」処理をするだけで 再度読み込むこともできます。 Web に置いてあるデータを利用する場合など手動のコピペも不要になので便利です。

四捨五入して文字列にするワークシート関数

Excel には、数値を四捨五入して文字列に変換してくれる 便利なワークシート関数があります。

次のようなものがあります。

第二引数は四捨五入する桁数で、省略すると 2 になります。

FIXED() ワークシート関数だけ、第三引数があり ここに TRUE (デフォルトは FALSE) を指定すると カンマ区切りなしで返します。

Excel の AREAS() ワークシート関数

Excel には AREAS() というワークシート関数があるのですが 使い方がややこしいので説明を書いてみました。

通常、COUNT() などのワークシート関数では 次のように複数の範囲をカンマで区切って使用することができます。

同じ範囲指定を AREAS() にも適用してみます。
（カッコを二重にします）

2 が返りました。

この AREAS() ワークシート関数は、引数の範囲指定に 何個の「範囲」が指定されたかを返してくれるわけです。

これだけだと使い道がよくわからないので、 さきほどと同じ範囲指定に、次のように名前を付けます。

今度は引数に名前を指定します。
（この場合は二重カッコは不要です）

当然、結果はさきほどと同じ値が返ります。

このように AREAS() ワークシート関数を使うと 範囲指定が見えないときに範囲の数を判定することができるわけです。

Excel の判定ワークシート関数

Excel には次のような判定用の関数があります。

ISBLANK() や ISNA() は セルの未入力や VLOOKUP で値がとれない場合の #N/A を判定できるので 使用頻度は高いですね。

また、エラーには、#DIV/0 (0 除算) など色々ありますが 細かく分類するには ERROR.TYPE() という関数を使います。

Excel で改行付きの書式を設定する

下の図のセル D2 には 2011/08/30 という値が入っています。 改行付きの書式 "dd (改行) (aaa)" を設定することで表示を改行させています。

書式を改行させるには、「セルの書式設定」で「ユーザ−定義」を選択して 入力中に [Ctrl]+[J] を押します。 Excel のセルの中で [Alt]+[Enter] を押すのと同じで複数行の入力ができるようになります。

これだけでなく、「配置」「文字の制御」で「折り返して全体を表示する」と 「縮小して全体を表示する」にチェックを入れます。 「縮小して全体を表示する」は「折り返して全体を表示する」にチェックを入れると 不活性になってしまうので、「折り返して全体を表示する」がチェックされていない状態で チェックしてやる必要があります。

この方法を使うと、は計算にも使える単純な日付を持ちつつも、 表示に関数を使わなくて済みます。

Excel の AVERAGE ワークシート関数

「Excel の COUNT ワークシート関数」で COUNT() と COUNTA() について書きましたが 相加平均 を取得する AVERAGE 関数にも AVERAGE() と AVERAGEA() があります。

COUNT と同じで AVERAGE() は数値のセルのみ、AVERAGEA() は空欄以外のセルの相加平均を取得します。

次のようになります。

どちらの関数も "-" （ハイフン）のセルは無視します。

数値が入っているセルだけ範囲に入れる場合と、 数値が入っていセルも範囲に入れたい場合で使い分けます。

Excel の COUNT ワークシート関数

Excel の COUNT() 関数は 「数値」のみを数える関数です。

例えば次のような場合、結果は 2 を返します。

ちなみに COUNTA() は空欄以外のセルを、
COUNTBLANK() は空欄のセルを数えます。

COUNTA()+COUNTBLANK() で全てのセルになります。

Excel の 2 つの値を比較するワークシート関数

Excel には、2 つの値が同じかどうかを判定してくれるワークシート関数があります。 次の EXACT() 関数は 2 つの値が同じ文字列の場合に TRUE になります。

数値でも同じ文字列になる場合は TRUE です。

他にも 2 つの値が同じ数値かどうかを判定してくれる DELTA() 関数があります。 （この関数は Excel2003 以前ではアドインの「分析ツール」を入れなければなりません）

DELTA() 関数は TRUE/FALSE ではなく 1/0 を返します。

値が数値に変換できない場合はエラーになります。
文字列でも数値に変換できる場合は数値にして比較します。

Excel の 階乗、順列、組み合わせのワークシート関数

Excel には 確率などの計算で使用する、階乗、順列、組み合わせのワークシート関数もあります。

まず「階乗」です。
数学では n! と書きます。

次に「順列」です。
数学では ₁₂P₄ のように書きます。

₁₂P₁₁ と ₁₂P₁₂ は どちらも 12!/1 なので同じになります。

12P11 = 12!/(12-11)! = 12!/1! = 12!/1
12P12 = 12!/(12-12)! = 12!/0! = 12!/1

最後に「組み合わせ」です。
数学では ₁₂C₄ のように書きます。

Excel で対戦結果などを管理する場合に 「組み合わせ」なんかがサッと使えると 便利なんじゃないでしょうか。

Excel の「名前を付けて保存」の「ファイルの種類」の初期値

Excel 2007 を使用していても周りが Excel 2000 や 2003 だと "Excel 97-2003 ブック" 形式で保存する必要があります。

しかし、Excel 2007 の「名前を付けて保存」ダイアログの「ファイルの種類」は 初期値が "Excel ブック" 形式なっています。

「ファイルの種類」の初期値は変更することができます。

まず 左上の丸いアイコンから「 Excel のオプション」を選択します。

「ファイルの保存形式」を "Excel 97-2003 ブック" に変更します。

これで「名前を付けて保存」ダイアログの「ファイルの種類」が "Excel 97-2003 ブック" になります。

Excel の TEXT ワークシート関数

Excel ではセルに書式を設定できますが、 TEXT ワークシート関数を使っても同じようなことができます。

書式は次のようになります。

=TEXT(値, 書式)

日本語曜日の書式 "aaa" を指定すると 次のようになります。

以前書いた HYPERLINK 関数と同様に数式の中で 使用できるのが大きなメリットです。

例えば次のように指定してみます。

=TEXT(B26, IF(YEAR(B26) < 2000, "yyyy/mm/dd", "yy/mm/dd"))

日付の年が 2000 年未満なら年を 4 桁、 そうでなければ 2 桁で表示します。

このような結果になります。

値に応じて書式を切り替えられるので便利です。

Excel のワークシート上のエラー

Excel には次の #N/A,#VALUE! などのワークシート上に 表示されるエラーがあります。

この他に、表示が ### になる場合があります。

数値を表示するエリアが狭い場合か、日付の計算結果がマイナスになる場合に ### になります。

ISERROR, ISERR, ISNA という関数で エラーを判定することができます。 判定できるエラーは次のようになります。

Excel の文字を変換するワークシート関数

Excel で、大文字から小文字、全角から半角など 文字を変換するワークシート関数を紹介します。

いずれの関数も「平仮名」など変換対象外の文字は 変換されず、そのまま残ります。

Excel の文字検索するワークシート関数について

Excel には文字検索用のワークシート関数として FIND と FIND があります。 この 2 つの関数の違いは、大文字・小文字の区別と、ワイルドカードが使用できるかどうかです。

"abcxyz" という文字列から "xyz" を検索してみます。

このような結果になります。
検索できなかった場合は、#VALUE! エラーになります。

Excel の文字置換するワークシート関数について

Excel には文字置換用のワークシート関数として REPLACE と SUBSTITUTE があります。

"ABCDE" という文字列の "BCD" を "XYZ" に置換します。

REPLACE 関数の書式は次のようになります。
開始位置と置換する文字数を指定して置換します。

=REPLACE(文字列, 開始位置, 文字数, 置換文字列)

SUBSTITUTE 関数の書式は次のようになります。

=SUBSTITUTE(文字列, 検索文字列, 置換文字列)

VB などの Replace 関数は Excel の REPLACE 関数ではなく SUBSTITUTE 関数の方と同じ指定なのがややこしいですね。

Excel の HYPERLINK ワークシート関数

Excel では、URL を貼り付けると自動的にハイパーリンクにしてくれる機能があります。 ワークシート関数を使っても ハイパーリンクを作成することができます。

HYPERLINK 関数は、次のような書式になります。

=HYPERLINK(リンク先, 別名)

この関数を使うメリットとしては、ハイパーリンクの生成の有無を数式に組み込めることです。

このように IF 関数を使って「 "http:" で始まる場合のみハイパーリンクを作成する」と いったこともできます。

Excel のちょっと変わったセル範囲の指定方法

Excel では通常 A1:B3 のようにセル範囲を指定しますが 次のように 青い範囲と赤い範囲を指定することで 共通部の紫の範囲を指定する、という方法があります。

範囲を半角スペースで区切って並べます。
（上の例では "B2:D6 C5:E8" ）

これにより、共通部分が指定できます。 範囲は 3 つ以上でも並べることができます。

Excel の切り上げ／切り捨てするワークシート関数

Excel のワークシート関数には、数値の切り上げ／切り捨てを してくれる関数が色々あります。

「基準値の倍数」というのは、基準値が 2 であれば、4, 6, 8...のことです。 次のように 100 や 1000, 0.01 などを指定すると桁数を指定したことになります。

ROUNDDOWN と TRUNC の違いは TRUNC が桁数を省略できることです。

Excel のワークシート関数で文字の棒グラフを表示する

以前、「Oracle で簡単棒グラフ表示」という ネタを書きましたが、Excel のワークシート関数でも同じようなことができます。

次のように "REPT" 関数を使用します。

"REPT" 関数の定義は次のようになります。

=REPT(繰り返す文字, 繰り返し回数)

次のようにすると 5 段階評価っぽくできます。

Excel の 進数を変換するワークシート関数

Excel には、2 進数を 10 進数や 16 進数に変換したり 10 進数を 16 進数に変換したりするワークシート関数があります。

関数は次のものがあります。

ただし、これらの関数は Excel2007 では普通に使えるのですが Excel2003 以前は「分析ツール」というアドオンを有効にしなくてはなりません。 （有効にするには Office の CD が必要です）

入力後、セルからフォーカスを移動させない

Excel では、セルに入力後、[Enter] を押すと フォーカスが移動してしまいます。 （デフォルトでは下に移動）

入力後にフォーカスを下以外に移動させたいときは 移動量が増えるので、少し面倒です。

入力後に移動させないためには、[Enter] を押すときに [Ctrl] も一緒に押します。

Excel で、アラビア数字をローマ数字に変換する

Excel には、アラビア数字 (1,2,3...) を ローマ数字 (I,II,III...) に変関してくれる関数が あります。

次のように ROMAN() 関数を使います。

=ROMAN(A1)

簡単に変換できます。

Excel の配列定数

Excel では、数値や文字だけでなく配列も扱うこともできます。

次のように定義します。

{1,2;3,4;5,6}

Excel の配列は、"{}" で括り、"," で列を分けて ";" で行を分けます。 上の定義は次のような 2 次元配列を意味しています。

1   2
3   4
5   6

配列定数は、ワークシート関数で、次の SUM() のように「範囲」を受ける引数に使用できます。

1 + 2 で 3 になります。

VLOOKUP() でも使用できます。

配列定数 {1,2;3,4;5,6} を指定していますが これをセル指定で表現すると次のようになります。

セルを使用しなくても色々できるので結構便利です。

Excel のフィル

Excel には「連続データの作成」という便利な技があります。

よく上の図のようにマウスで範囲を広げて生成しているのを 見かけるのですが、範囲が広い場合、マウスがスライドし過ぎて かなり下の行まで行ってしまうことがあります。

これを防ぐというか、 そもそも「連続データの作成」は、『フィル』という機能の中にあるので 編集メニューから選んで実行することができます。

まず範囲を選択します。 このときはドラッグ＆ドロップする必要はないので [Shift] キーなどを使ってゆっくり選択できます。

次にメニューから「編集」→「フィル」→「連続データの作成」と 選択します。

ポップアップウィンドウが出ます。
1 ずつ加算していく場合はデフォルトで OK です。

これで連続データが作成できました。

『フィル』には他にも機能がありますが あまり使うことはないと思います。 ドラッグ＆ドロップによる「連続データの生成」で マウスが滑って どうしても上手く選択できないときに この機能を思い出してみてください。

Excel の [Ctrl]+[End] / [End]

Excel で [End] キーは、ちょっと変わった移動です。

Excel では、次のように 「入力された一番右の列」「入力された一番下の行」の 交わるセルを、最終有効セルとして記憶しています。

デフォルトでは、最終有効セルまでが印刷範囲になります。

[Ctrl]+[End] は、カーソルを最終有効セルに移動させます。

最終有効セルがどこにあるかわからなくなったときや データの最終行に移動したいときに便利です。

ちなみに、入力した値をクリアしても「入力された一番右の列（下の行）」は変わりません。 最終有効セルを内側に移動させるためには、行や列を削除します。

次に [End] キーだけを 1 回押すと、 右下に次のように "END" と表示されます。

この状態で カーソルキーを押すと その方向に大きく移動します。
移動する内容は [Ctrl]+[カーソル] と同じです。

黄緑がカーソルの現在の位置、黄色が各方向の移動です。

値の入ったセルがなければ 行や列の先頭や後尾に、 値の入ったセルがあれば、連続して値の入っているセルの端に カーソルを移動させます。

カーソルの代わりに [Home] を押すと [Ctrl]+[End] と同じで 最終有効セルに移動します。

これら同じ動きの移動コマンドがあるので 自分が使い易いものを選んで覚えると良いと思います。

Excel でシートを Very 非表示にする

Excel では、メニューからシートを非表示にすることができます。

ただし、この方法の場合、簡単に再表示させることができます。

別の方法で、「再表示」を表示しないようにすることができます。

まず、どこからでも良いので（下の図は「コードの表示」）で Visual Basic Editor を起動します。

必要なのは、下の赤枠の「プロパティ ウィンドウ」なので 表示されて居ない場合は [F4] を押して表示します。

このプロパティウィンドウの一番下の項目に "Visible" があります。 このプロパティを "2 - xlSheetVeryHidden" に設定します。

これで、シートは非表示になり メニューから見ても「再表示」が不活性なままになりました。

ちなみに "Visible" プロパティですが、Excel のメニューから 通常のやりかたで非表示にした場合は、"0 - xlSheetHidden" になります。 Excel からでは、"-1 - xlSheetVisible" と "0 - xlSheetHidden" の 切り替えしかできないわけです。

Excel で「名前」の一覧を貼り付ける

Excel で「名前」を付けていると 多くなりすぎて 何が何やらわからなくなるときがあります。 少なくとも私にはあります。

そんなときに、「名前」で定義されている内容を セルに出力する機能があります。

メニューから「挿入」→「名前」→「貼り付け」を選択します。

名前の貼り付けウィンドウが出てくるので 「リスト貼り付け (L) 」ボタンをクリックします。

カーソルの当たっていたセルに 「名前」のリストが出力されます。

Excel のワークシート関数 PHONETIC

以前、「 Excel の ふりがな」といネタを書きましたが この値を別のセルで取得するためのワークシート関数 PHONETIC があります。

使い方は次のように簡単です。

平仮名、片仮名は「ふりがな」の設定に依存します。

通常の「ふりがな」の表示と少し違うのは、 常にセルの文字全ての「ふりがな」が表示されることです。

左のセル内の「ふりがな」は、「ほんじつ」と「ひがら」だけですが 右の関数セルでは、「ほんじつはおひがらも」と表示されます。

IE で Office2007 形式をダウンロードすると zip になる

Vista 環境の IE 7 で Office2007 形式のファイル (.docs, .xlsx, .pptx) を ダウンロードすると zip ファイルとしてダウンロードされてしまう、という現象が発生しました。

Web サーバに Office2007 形式用の MIME タイプを設定しないと IE が勝手に中身を見て判断してしまうようです。

追加する MIME タイプは以下のようになります。

【参考サイト】
Microsoft - サード パーティのサーバー上のファイル拡張子を登録します。

設定すると Office のドキュメントとして認識されるようになります。

Excel のゼロを非表示にする書式

以前、シートのオプションで 0 を非表示にする「Excel のゼロの非表示」を 書きましたが、個別に設定するもっと良い方法があったので紹介します。

Excel の数値の書式には ";" (セミコロン) でプラス、マイナスの場合を分けることができます。

0.0;(0)

この書式では、プラスの時は“小数点１桁まで表示”、マイナスの時は“カッコで囲んで表示”になります。

書式を 2 つに分けた場合、ゼロはプラスの書式で処理されます。

書式を 3 つに分けた場合、3 つ目がゼロの書式になります。

0.0;(0);[赤]0

この書式では、ゼロのときだけ赤字になります。

このゼロの書式を空にすることで ゼロを非表示にできます。

";"(セミコロン) が 1 つの場合と 2 つの場合では マイナス値を扱う書式が違ってくるので要注意です。

0.0;(0);  ← ゼロは非表示
0.0;(0)   ← ゼロはプラスの書式で表示

Excel で 繰り返して使う機能をロック

あなたは知ってる？ダブルクリックだけでできるExcelの小技いろいろ - IDEA*IDEA 〜 百式管理人のライフハックブログ

この記事を読んで知ったのですが、ダブルクリック技の 1 つに 「繰り返して使う機能をロック」という機能があるそうです。

Excel 2000 で試してみたところ、 「書式のコピー/貼り付け」しか確認できなかったのですが とても便利だと感じたので紹介します。

まずは通常の使い方から。

「書式のコピー/貼り付け」をクリックします。
アイコンは凹んだ状態になります。

その状態で別のセルをクリックします。

クリックしたセルに書式が貼り付けられます。
同時に「書式のコピー/貼り付け」のアイコンも元に戻ります。

今度は「書式のコピー/貼り付け」をダブルクリックします。
やはりアイコンは凹んだ状態になります。

ただし、ダブルクリックした場合は、 別のセルに書式を貼り付けても、アイコンが凹んだままになります。

続けて、他のセルにも書式を貼り付け続けることができます。

書式のコピペは 結構使用するので覚えておくと役に立ちそうです。

Excel のゼロの非表示

他人の作った Excel で 値 0 を 次のような条件付書式で 非表示にしているシートがありました。

一部分の 0 だけであれば これでも良いのですが シート全体の場合、 オプションで非表示にすることができます。

「ツール」→「オプション」の「表示」タブに『ゼロ値』という オプションがあります。（ Excel2000 の場合）

このオプションのチェックを外すと、次のようなシートの 0 が・・・

消えてしまいます。

このオプションは、設定したシートのみで有効で シートに保存されるので、そのまま人に渡すこともできます。

Excel は、オプションによって シートのみで有効のものや、Excel 全体や ブックで有効なものあるので 難しいです。

Excel の [Ctrl]+[Enter]

Excel の入力系の技の 1 つに [Ctrl] を押しながら入力 (Enter) というものがあります。

それほど使う頻度は高くないと思いますが どちらもよく使用するキーなので 簡単に指で覚えることができます。

まず範囲を選択します。

次に値を入力します。

この状態で [Enter] だけを押すと、 通常の“選択範囲内での入力”になり 隣のセルにフォーカスが移動するだけです。

しかし、ここで [Ctrl] を押しながら [Enter] を押します。

選択していた範囲のセル全てに 入力した値が設定されました。

もちろん、これだけではありません。
このショートカットは、数式を入力するときに真価を発揮します。

例えば、"=row()" を入れてみます。

行番号が表示されました。

どのセルにも "=row()" が入るわけですが 当然値が違います。

集計する場合も便利です。

E13:E15 のようなセル指定も セルをコピーするときと同様に 展開してくれます。

慣れるまでは、1 つのセルに入力した値をコピーする方が 楽なように感じると思いますが 多くの場合、コピーする範囲は最初から決まっているので 範囲を指定してからこの方法で入力する方が キーを押す回数も少なくて速くなると思います。

Excel の ふりがな

Excel には「ふりがな」を扱う機能があります。

昔からメニューにあるのですが、意外と知られていないようです。

表示すると次のようになります。

「ふりがな」の値は、セルに値を入力すときに 自動で設定してくれます。 ですので、漢字一文字ずつを違う読みで入力した場合 次のように変な感じに設定されてしまうことがあります。

Office 2000 の場合、「書式」の中にメニューがあります。

「設定」では、平仮名や片仮名、表示位置の設定ができます。 「編集」では、現在設定されている値を変更することができます。

結構重要なことですが、並び替えを行なうときは この「ふりがな」の値が利用されます。 ですので、同じ“生”という漢字を使っても 次のように並んでしまいます。

テキストエディタなどから貼り付けた場合は 「ふりがな」が設定されません。 次の図の右の列は「ふりがな」が設定されていないため 見たままの並び順になっています。

Excel から UTF-8 で出力する

Excel のマクロなど UTF-8 で出力する モジュールを紹介します。

UTF-8ファイル作成 for VBA

PostgreSQL の テーブルレイアウトを Excel で作成して Excel のマクロで CREATE 文を生成しているのですが コメント文など 日本語を UTF-8 で出力するのに 利用させてもらいました。

使い方も簡単で、ダウンロードした class ファイルを Excel にインポートして、Open メソッドの代わりに 次のように記述します。

Dim f1 As New TextFile

f1.FileCreate "test1.txt", "UTF-8"
f1.TextWrite "このファイルは UTF-8 でエンコードされています。"
f1.FileClose

リボンの表示モード切替

Word や Excel など MS Office の 2007 には 「リボン」という機能があります。 この機能、 2007 から使い始めたユーザなら 便利なのかもしれないのですが 今までの Office ユーザには どうも不評のようです。 （少なくとも私の周りでは不評です）

そんな話をしているときに 教えてもらった小技なのですが このリボン、タブの部分をダブルクリックすると 『リボン出っ放し』モードと 『タブをクリックでリボン表示、別の場所クリックでリボン非表示』モードを 切り替えることができるのです。

タブをダブルクリックすると 下のように リボンが非表示になります。

画面がすっきり広くなりました。

再度タブをダブルクリックすると『リボン出っ放し』モードに戻ります。

リボンは設定でも非表示にできますが、この方法だと どちらにも簡単に切り替えることができるので便利です。

Excelで表の範囲を一気に選択する

Excelで下の図のように表を作っている場合 表の範囲のセルを選択して枠線を付けたい時があります。

そんなときに使えるショートカットキーが、 [Ctrl]+[*]です。

テンキーの "*"（アスタリスク）が使えない場合は、 [Ctrl]+[Shift]+[:]です。（[Shift]+[:]が[*]になります）

下の図のように、表の範囲内にカーソルを置きます。

すかさず、[Ctrl]+[*]です。

表の範囲を囲むことができました。

あとは普通に罫線で枠線を付けます。

できあがりです。

このショートカットキーで囲める範囲は、 『Excelが表と認識できる範囲』になります。

つまり、途中の行や列に空きがあったりすると、 Excelは、そこで表が終わっていると判断します。 これは、フィルタや並び替えの時も同じです。 このショートカットキーで、 「Excelがどこまでを表と認識しているか」を 確認してみるのも良いかもしれません。

Excelで複数のセルをお手軽に入力する

Excelで下の "a"〜"f" の様に処理したいセルがある場合 入力の度にカーソルやマウスを使わなくても清む方法があります。

準備として、連続して処理したいセルを「Shift+移動キー」や マウスやのドラッグ＆ドロップで選択します。

その後は、[Enter]を押すだけで、下の矢印の順番に フォーカスが移動します。

つまり、入力→[Enter]を繰り返すだけで、選択した範囲内だけを フォーカスがグルグル移動してくれるわけです。

ちなみに、[Shift+Enter]を押すと、逆に移動します。

下のように、マウスで四角形以外の選択をしても その範囲内をフォーカスが移動してくれます。 ただし、マウスで指定する場合は、指定した順番で フォーカスが移動するので注意してください。