EXCEL FAQ3

20090123という文字列を日付データに変換するには
=TEXT(A1,"0000!/00!/00")*1
39836 などの数値(シリアル値)が表示された場合は、セルの書式を[日付]に変更してください。

平成1年を平成元年と表示するには
=IF(TEXT(A1,"ge")="h1","平成元年" & TEXT(A1,"m月d日"),TEXT(A1,"ggge年m月d日"))
月日も漢数字を使って表示させるなら
=IF(TEXT(A1,"ge")="h1","平成元年" & TEXT(A1,"[DBNum1]m月d日"),TEXT(A1,"[DBNum1]ggge年m月d日"))
ユーザー定義の書式で、ggge"年"m"月"d"日" では 平成1年 と表示されてしまいます。(1989/1/8から平成です)

西暦年から干支を表示する (A1に西暦4桁)
=CHOOSE(MOD(A1,12)+1,"申","酉","戌","亥","子","丑","寅","卯","辰","巳","午","未")

もし今日が1日〜15日なら締切を当月20日と表示、もし今日が16日〜31日なら翌月の20日として表示したい
=DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>15),20)

時間を10進法で表示するには (1:30 なら 1.5 とする)
=A1*24
[セルの書式設定]-[表示形式]タブで[分類]を「標準」にしてください。
10進数を 時分で表示にするには (1.5 なら 1時間30分 と表示する)
=TEXT(A1/24,"[h]時間m分")
時刻の計算 8:00 + 15  なら 8:15 ( A1:時刻  B1:数値の場合)
=A1+TIME(0,B1,0)
時刻の計算 3:50 の 6.5 時間前の時刻は (A1:時刻  B1:数値 の場合)
=A1-B1/24+(A1<B1/24)

勤務時間計算を行うには (A2セルに出社時間、B2セルに退社時間、休憩が1時間の例)
=B2-A2-TIME(1,0,0)
セル書式を [h]:mm とすると、24時間を超える表示ができます。また、休憩時間が45分なら -TIME(0,45,0) とします。

指定列の最下セルを参照するには(範囲をA列全体とした数式例)
【数値】=LOOKUP(10^15,A:A)
【文字列】=LOOKUP("ーー",A:A)
【数値&文字列】=LOOKUP(1,0/(A:A<>""),A:A)
指定列の最上セルを参照するには(範囲をA列全体とした数式例です。実際の範囲を指定してください)
【数値】=INDEX(A:A,MATCH(0,INDEX(0/A:A,),0))
【文字列】=VLOOKUP("*",A:A,1,)
【数値&文字列】=INDEX(A:A,MATCH(1,INDEX(1/(A:A<>0),),0))

指定行の中で最右セルの数値(行1の場合)
=LOOKUP(10^15,1:1)
【列範囲指定の場合】=LOOKUP(10^15,A1:H1)
指定行の中で最右セルの文字列(行1の場合)
=INDEX(1:1,MATCH("",1:1,-1))
【列範囲指定の場合】=INDEX(A1:H1,MATCH("",A1:H1,-1))
<参考>
▼行1の中で文字列が "小計" の左3列目セルの文字列、値は
=INDEX(1:1,MATCH("小計",1:1,0)-3)

1行おきの合計
奇数行のみ合計(行1,3,5,7,9 ...)
=SUMPRODUCT((MOD(ROW(A1:A100),2)=1)*(A1:A100))
{=SUM(IF(MOD(ROW(A1:A100),2)=1,A1:A100,""))}  ←配列数式 Ctrl+Shift+Enter
=SUMPRODUCT(INDIRECT("a1:a100"),MOD(ROW(INDIRECT("a1:a100")),2))
偶数行のみ合計(行2,4,6,8,10 ...)
=SUMPRODUCT((MOD(ROW(A1:A100),2)=0)*(A1:A100))
{=SUM(IF(MOD(ROW(A1:A100),2)=0,A1:A100,""))}  ←配列数式 Ctrl+Shift+Enter
=SUMPRODUCT(INDIRECT("a1:a100"),MOD(ROW(INDIRECT("a1:a100"))+1,2))
1列おきの合計
奇数列のみ合計(列A,C,E,G,I,K,M)
=SUMPRODUCT((MOD(COLUMN(A1:N1),2)=1)*(A1:N1))
{=SUM(IF(MOD(COLUMN(A1:N1),2)=1,A1:N1,""))}  ←配列数式 Ctrl+Shift+Enter
=SUMPRODUCT(INDIRECT("A1:N1"),MOD(COLUMN(INDIRECT("A1:N1")),2))
偶数列のみ合計(列B,D,F,H,J,L,N)
=SUMPRODUCT((MOD(COLUMN(A1:N1),2)=0)*(A1:N1))
{=SUM(IF(MOD(COLUMN(A1:N1),2)=0,A1:N1,""))}  ←配列数式 Ctrl+Shift+Enter
=SUMPRODUCT(INDIRECT("A1:N1"),MOD(COLUMN(INDIRECT("A1:N1"))+1,2))
A4,A6,A8...と範囲の偶数セルのみ対象にして平均を求めるには
=SUMPRODUCT((MOD(ROW(A4:A100),2)=0)*(A4:A100))/SUMPRODUCT((MOD(ROW(A4:A100),2)=0)*ISNUMBER(A4:A100))
または、
{=AVERAGE(IF((MOD(ROW(A4:A100),2)=0)*ISNUMBER(A4:A100),A4:A100))}  ←配列数式 Ctrl+Shift+Enter

文字列に、鰍ゥ汲含んでいる場合、"環境依存" と表示する
=IF(COUNT(INDEX(FIND({"",""},A1),)),"環境依存","")
文字列から特殊文字を削除する [Module1]  
Function CleanX(Src As Variant) As String
'特殊文字を削除します
Application.Volatile
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[\u00A0-\u3004\u3012\u3220-\u33FE]"
CleanX = .Replace(Src, "")
End With
End Function


ユーザー定義関数 =CleanX(A1)

A列に整数1〜1000までの番号あり、欠番のみB列に取り出すには(数式を下にコピー)
=IF(AND(ROW(A1)<MAX(A:A),COUNTIF(A:A,ROW(A1))=0),ROW(A1),"")
セルの文字列を一文字単位で別セルに取り出す方法
(例)A1セルに12345 なら B1に1、 C1に2、 D1に3、 E1に4、 F1に5
B1セルに =MID($A1,COLUMN()-1,1)
と入力して右方向へコピーします。
参考:数値、文字列共に取り出せます。
A列とB列を比較してC列に↑、↓、-、 と記号表示する(数値のみ)
=IF(COUNT(A1:B1)<2,"",TEXT(B1-A1,"↑;↓;-"))

4と9を飛ばした数字の連続データを作成
A1セルに1を入力し、以下数式を下にコピー
A2セルに =IF(OR(RIGHT(A1)="3",RIGHT(A1)="8"),A1+2,A1+1)
または、B1セルに数式入力して右にコピーします。
重複なしのランダム数値(抽選)
A1 に式 =RAND() を入力して下へ抽選数値分までフィルコピー
任意セルに下記数式を必要分下へフィルコピー
=MATCH(SMALL(A$1:A$2000,ROW(A1)),A$1:A$2000,0)
指定範囲のセル値をランダムに表示する方法
(例)A1〜A9の範囲データを参照する場合
=INDEX(A1:A9,RAND()*9+1)
▼カタカナをランダムに5文字表示
=CHAR(RANDBETWEEN(9506,9587))&CHAR(RANDBETWEEN(9506,9587))&CHAR(RANDBETWEEN(9506,9587))&CHAR(RANDBETWEEN(9506,9587))&CHAR(RANDBETWEEN(9506,9587))
▼ひらがなをランダムに表示
=CHAR(RANDBETWEEN(9250,9331))
▼大文字のアルファベットをランダムに表示
=CHAR(RANDBETWEEN(65,90))
▼小文字のアルファベットをランダムに表示
=CHAR(RANDBETWEEN(97,122))

Excel ショートカットキー
Ctrl キーを使用するショートカット キー
Ctrl + Shift + ) 選択範囲内にある非表示の行を表示します。
Ctrl + Shift + 0 選択範囲内にある非表示の列を表示します。
Ctrl + Shift + & 選択したセルに外枠罫線を適用します。
Ctrl + Shift + 下線 (_) 選択したセルから外枠罫線を削除します。
Ctrl + Shift + ^ [標準] 表示形式を設定します。
Ctrl + Shift + $ [通貨] 表示形式 (\-1,234) を設定します。
Ctrl + Shift + % [パーセンテージ] 表示形式 (小数点以下の桁数 0) を設定します。
Ctrl + ^ [指数] 表示形式 (小数点以下の桁数 2) を設定します。
Ctrl + Shift + # [日付] 表示形式 (yyyy/mm/dd) を設定します。
Ctrl + @ [時刻] 表示形式 (hh:mm、24 時間表示) を設定します。
Ctrl + Shift + ! 桁区切りの表示形式を設定します。負の値には負符号 (-) を使用します。
Ctrl + Shift + アスタリスク (*) アクティブ セル領域 (アクティブ セルを含み、空白の行と列で囲まれているデータ領域) を選択します。
ピボットテーブル レポートで、ピボットテーブル レポート全体を選択します。
Ctrl + セミコロン (;) 現在の日付を入力します。
Ctrl + Shift + 二重引用符 (") アクティブ セルの 1 つ上のセルの値を、アクティブ セルおよび数式バーにコピーします。
Ctrl + Shift + 正符号 (+) [挿入] ダイアログ ボックスを表示して、空白セルを挿入します。
Ctrl + 負符号 (-) [削除] ダイアログ ボックスを表示して、選択されたセルを削除します。
Ctrl + コロン (:) 現在の時刻を入力します。
Ctrl + Shift + アクセント記号 (`) 数式と計算結果の表示を切り替えます。
Ctrl + Shift + 単一引用符 (') アクティブ セルの 1 つ上のセルの数式を、アクティブ セルまたは数式バーにコピーします。
Ctrl + 1 [セルの書式設定] ダイアログ ボックスを表示します。
Ctrl + 2 太字の書式の設定と解除を切り替えます。
Ctrl + 3 斜体の書式の設定と解除を切り替えます。
Ctrl + 4 下線の設定と解除を切り替えます。
Ctrl + 5 取り消し線の設定と解除を切り替えます。
Ctrl + 6 オブジェクトの表示、非表示、および位置のみの表示を切り替えます。
Ctrl + 8 アウトライン記号の表示と非表示を切り替えます。
Ctrl + 9 選択した行を非表示にします。
Ctrl + 0 選択した列を非表示にします。
Ctrl + A ワークシート全体を選択します。
ワークシートにデータが入力されている場合は、Ctrl + A キーを 1 回押すと、アクティブ セル領域を選択します。
Ctrl + A キーを 2 回押すと、アクティブ セル領域とその集計行を選択します。
Ctrl + A キーを 3 回押すと、ワークシート全体を選択します。
数式でカーソルが関数名の右にある場合は、[関数の引数] ダイアログ ボックスを表示します。
数式でカーソルが関数名の右にある場合は、Ctrl + Shift + A キーを押すと、引数名とかっこを挿入します。
Ctrl + B 太字の書式の設定と解除を切り替えます。
Ctrl + C 選択されたセルをコピーします。
Ctrl + C キーを押し、Ctrl + C キーをもう一度押すと、クリップボードを表示します。
Ctrl + D [下方向へコピー] を使用して選択範囲内で下方向のセルに内容と書式をコピーします。
Ctrl + F [検索と置換] ダイアログ ボックスの [検索] タブを表示します。
このタブは、Shift + F5 キーを押しても表示できます。また、Shift + F4 キーを押すと、[検索] タブの前回の操作を繰り返します。
Ctrl + Shift + F キーを押すと、[セルの書式設定] ダイアログ ボックスの [フォント] タブを表示します。
Ctrl + G [ジャンプ] ダイアログ ボックスを表示します。
F5 キーを押しても [ジャンプ] ダイアログ ボックスを表示できます。
Ctrl + H [検索と置換] ダイアログ ボックスの [置換] タブを表示します。
Ctrl + I 斜体の書式の設定と解除を切り替えます。
Ctrl + K ハイパーリンクを挿入するための [ハイパーリンクの挿入] ダイアログ ボックスを表示します。または、選択された既存のハイパーリンクを編集するための [ハイパーリンクの編集] ダイアログ ボックスを表示します。
Ctrl + N 新しい、空白のブックを作成します。
Ctrl + O [ファイルを開く] ダイアログ ボックスを表示してファイルを開くか、見つけます。
Ctrl + Shift + O コメントが入力されたセルをすべて選択します。
Ctrl + P [印刷] ダイアログ ボックスを表示します。
Ctrl + Shift + P [セルの書式設定] ダイアログ ボックスの [フォント] タブを表示します。
Ctrl + R [右方向へコピー] を使用して選択範囲内で右方向のセルに内容と書式をコピーします。
Ctrl + S 作業中のファイルを現在のファイル名、場所、およびファイル形式で保存します。
Ctrl + T [テーブルの作成] ダイアログ ボックスを表示します。
Ctrl + U 下線の設定と解除を切り替えます。
Ctrl + Shift + U 折りたたまれた数式バーを展開し、展開されていた数式バーを折りたたみます。
Ctrl + V クリップボードの内容をカーソルの位置に挿入します。範囲を選択している場合は、選択範囲の内容をクリップボードの内容に置き換えます。オブジェクト、テキスト、セルの内容を切り取った後、またはコピーした後でのみ、使用できます。
Ctrl + Alt + V [形式を選択して貼り付け] ダイアログ ボックスが表示されます。この機能を使用できるのは、ワークシートや他のプログラムで、オブジェクト、テキスト、セルの内容を切り取った後、またはコピーした後だけです。
Ctrl + W Ctrl + W 選択されたブックのウィンドウを閉じます。
Ctrl + X Ctrl + X 選択されたセルを切り取ります。
Ctrl + Y Ctrl + Y 最後のコマンドまたは操作を繰り返します。
Ctrl + Z Ctrl + Z [元に戻す] を使用して、最後のコマンドの操作を元に戻すか、最後に入力した内容を削除します。
オートコレクト スマート タグが表示されている場合
Ctrl + Shift + Z [元に戻す] または [やり直し] を使用して最後に行ったオートコレクトを元に戻します (または、繰り返します)。
ファンクション キー
F1 [Excel ヘルプ] 作業ウィンドウを表示します。
Ctrl + F1 Microsoft Office Fluent ユーザー インターフェイス のコンポーネントであるリボンの表示と非表示を切り替えます。
Alt + F1 現在の範囲からグラフを作成します。
Alt + Shift + F1 ワークシートを挿入します。
F2 アクティブなセルの内容の最後にカーソルを配置します。セル内の編集がオフになっている場合は、カーソルを数式バーに移動します。
Shift + F2 セルのコメントを追加または編集します。
Ctrl + F2 印刷プレビュー ウィンドウを表示します。
F3 [名前の貼り付け] ダイアログ ボックスを表示します。
Shift + F3 [関数の挿入] ダイアログ ボックスを表示します。
F4 直前のコマンドまたは操作を繰り返します。
Ctrl + F4 選択したブック ウィンドウを閉じます。
F5 [ジャンプ] ダイアログ ボックスを表示します。
Ctrl + F5 選択したブック ウィンドウを元のサイズに戻します。
F6 ワークシート、リボン、作業ウィンドウ、およびズーム コントロール間で移動します。分割されたワークシート ([表示] メニュー、[このウィンドウの管理]、[ウィンドウ枠の固定]、[ウィンドウの分割]) では、F6 キーでウィンドウとリボン領域間だけでなく、分割されたウィンドウ間でも移動します。
Shift + F6 ワークシート、ズーム コントロール、作業ウィンドウ、およびリボン間で移動します。
Ctrl + F6 複数のブック ウィンドウが開いている場合は、次のブック ウィンドウに移動します。
F7 [スペルチェック] ダイアログ ボックスを表示して、作業中のワークシートまたは選択した範囲内のスペルをチェックします。
Ctrl + F7 ブック ウィンドウが最大化されていない場合は、[移動] コマンドの操作を開始します。方向キーを使用してウィンドウを移動し、操作が完了したら Enter キーを押します。Esc キーで移動操作を取り消すことができます。
F8 拡張選択モードを切り替えます。拡張選択モードでは、ステータス バーに "選択範囲の拡張" と表示され、方向キーで選択範囲を拡張できます。
Shift + F8 隣接しないセルまたは範囲を選択範囲に追加できます。追加する範囲の先頭セルに方向キーで移動し、F8 キーを押し、方向キーで範囲を拡張します。
Ctrl + F8 ブック ウィンドウが最大化されていない場合は、[サイズ変更] コマンドを実行します (コントロール メニュー)。方向キーを使用してウィンドウのサイズを変更できます。操作が完了したら Esc キーを押します。
Alt + F8 [マクロ] ダイアログ ボックスを表示します。このダイアログ ボックスで、マクロの作成、実行、編集、削除を行うことができます。
F9 開いているブックのすべてのワークシートを計算します。
Shift + F9 作業中のワークシートを計算します。
Ctrl + Alt + F9 最後に計算されてから変更されているかどうかにかかわらず、開いているブックのすべてのワークシートを計算します。
Ctrl + Alt + Shift + F9 依存数式を再度チェックし、要計算としてマークされていないセルも含め、開いているブックのすべてのセルを計算します。
Ctrl + F9 ブック ウィンドウを最小化します。
F10 キー ヒントをオンまたはオフにします。
Shift + F10 選択されたアイテムのショートカット メニューを表示します。
Alt + Shift + F10 スマート タグのメニューまたはメッセージを表示します。複数のスマート タグが表示されている場合は、次のスマート タグに切り替えてメニューまたはメッセージを表示します。
Ctrl + F10 選択したブック ウィンドウを最大化するか、または元に戻します。
F11 現在の範囲からグラフを作成します。
Shift + F11 ワークシートを挿入します。
Alt + F11 Microsoft Visual Basic Editor を起動します。Visual Basic for Applications (VBA) を使用して、マクロを作成できます。
F12 [名前を付けて保存] ダイアログ ボックスを表示します。
上記以外の便利なショートカット キー
方向キー ワークシート内で、上、下、左、または右のセルに移動します。
Ctrl キーを押しながら方向キー ワークシート内の現在のデータ範囲 (データ範囲 : データが入力されていて、周囲が空白セルまたはシートの端で囲まれているセル範囲。)の先頭行、末尾行、左端列、または右端列に移動します。
Shift + 方向キー 選択範囲を上、下、左、または右に拡張します。
Ctrl + Shift + 方向キー アクティブ セルと同じ行または列にある空白以外の最後のセルまで選択範囲を拡張してアクティブ セルにします。次のセルが空白の場合は、選択範囲を次の空白以外のセルまで拡張します。
リボンが選択されているとき、左方向キーまたは右方向キー それぞれ左または右のタブを選択します。サブメニューが開いているか選択されている場合は、メイン メニューとサブメニューを切り替えます。リボン上のタブが選択されている場合は、タブ上のボタン間を移動します。
下方向キーまたは上方向キー メニューまたはサブメニューが開いている場合は、前または次のコマンドを選択します。リボン上のタブが選択されている場合は、前または次のタブ グループに移動します。
ダイアログ ボックスでは、方向キーを使用して、アクティブなドロップダウン リスト ボックス内、またはオプション グループ内を移動します。
↓キーまたは Alt + ↓キー 選択したドロップダウン リストを開きます。
BackSpace 数式バーで、左にある文字を 1 文字削除します。
アクティブなセルの内容を消去します。
セルの直接編集モードでは、カーソルの左側の文字を削除します。
Del 選択したセルの書式やコメントを維持したまま、セルの内容 (データや数式) を削除します。
セルの編集モードで、カーソルの右の文字を削除します。
End スクロール ロックがオンの場合は、ウィンドウの右下隅のセルに移動します。
メニューまたはサブメニューが表示されている場合は、そのメニューまたはサブメニューの末尾のコマンドを選択します。
Ctrl + End ワークシートの最も下の行の右端の列にある最後のセルに移動します。カーソルが数式バーにあるときは、文字列の末尾にカーソルを移動します。
Ctrl + Shift + End ワークシート上のデータが入力されている最後 (右下隅) のセルまで選択範囲を拡張します。カーソルが数式バーにあるときは、カーソル位置から末尾までのすべての文字を選択します。このとき、数式バーの高さは変化しません。
Enter セルまたは数式バーの入力を確定し、下のセルを選択します (既定)。
データ フォームで、次のレコードの最初のフィールドに移動します。
選択したメニューを開く (F10 を押してメニュー バーを選択)、または選択したコマンドの操作を実行します。
ダイアログ ボックスで、ダイアログ ボックスの既定のコマンド ボタン ([OK] など、太枠のボタン) の操作を実行します。
Alt + Enter セル内で改行します。
Ctrl + Enter 選択したセル範囲に、アクティブ セルと同じ値を入力します。
Shift + Enter セルの入力を確定し、すぐ上のセルを選択します。
Esc セルまたは数式バーの入力を取り消します。
開いているメニューやサブメニュー、ダイアログ ボックス、メッセージ ウィンドウを閉じます。
または、全画面表示モードになっている場合はそのモードを閉じ、通常表示モードに戻してリボンおよびステータス バーを再度表示します。
Home ワークシートの最初の行に移動します。
スクロール ロックがオンの場合は、ウィンドウの左上隅のセルに移動します。
メニューまたはサブメニューが表示されている場合は、そのメニューまたはサブメニューの先頭のコマンドを選択します。
Ctrl + Home ワークシートの先頭に移動します。
Ctrl + Shift + Home 選択範囲をワークシートの先頭のセルまで拡張します。
PageDown ワークシート内で 1 画面下にスクロールします。
Alt + PageDown ワークシート内で 1 画面右にスクロールします。
Ctrl + PageDown ブック内で次のシートに移動します。
Ctrl + Shift + PageDown ブック内の現在のシートと次のシートを選択します。
PageUp ワークシート内で 1 画面上にスクロールします。
Alt + PageUp ワークシート内で 1 画面左にスクロールします。
Ctrl + PageUp ブック内で前のシートに移動します。
Ctrl + Shift + PageUp ブック内の現在のシートと前のシートを選択します。
Space ダイアログ ボックスで、選択したボタンの操作を実行するか、またはチェック ボックスのオンとオフを切り替えます。
Ctrl + Space 選択範囲を列全体に拡張します。
Shift + Space 選択範囲を行全体に拡張します。
Ctrl + Shift + Space ワークシート全体を選択します。
Ctrl + Shift + Space キーを 1 回押すと、アクティブ セル領域を選択します。
Ctrl + Shift + Space キーを 2 回押すと、アクティブ セル領域とその集計行を選択します。
Ctrl + Shift + Space キーを 3 回押すと、ワークシート全体を選択します。
オブジェクトが 1 つ選択されている場合 Ctrl + Shift + Space キーを押すと、シートにあるすべてのオブジェクトを選択します。
Alt + Space Microsoft Office Excel ウィンドウのコントロール メニューを表示します。
Tab ワークシート内の右のセルに移動します。
保護されているワークシートで、ロックされていないセル間を移動します。
ダイアログ ボックスで、次のオプションまたはオプション グループに移動します。
Shift + Tab ワークシート内の前のセルに移動します。または、ダイアログ ボックスで、前のオプションに移動します。
Ctrl + Tab ダイアログ ボックスで、次のタブに切り替えます。
Ctrl + Shift + Tab ダイアログ ボックスで、前のタブに切り替えます。

シート1には[商品コード][品名][単価]、シート2で[商品コード]を入力すると対応する品名、単価を表示させるには
前提:Sheet2 の A2セルに[商品コード]を入力する場合の数式
B2 =IF(ISERROR(VLOOKUP($A2,Sheet1!$A:$C,COLUMN(),0)),"",VLOOKUP($A2,Sheet1!$A:$C,COLUMN(),0))
または、
INDEXとMATCH関数を利用する数式(1行目にSheet1の同タイトルを記入)
B2 =IF(ISERROR(INDEX(Sheet1!$A:$C,MATCH($A2,Sheet1!$A:$A,0),MATCH(B$1,Sheet1!$1:$1,0))),"",INDEX(Sheet1!$A:$C,MATCH($A2,Sheet1!$A:$A,0),MATCH(B$1,Sheet1!$1:$1,0)))

ロック(保護)されているセルに色を付ける方法【条件付き書式の設定】
(1)シート全体を選択します。 [Ctrl]+「A」
(2)【書式】→【条件付き書式】を選択し、
 【条件付き書式の設定】ダイアログで『数式が』
=CELL("PROTECT",A1)=1 と入力し[書式]ボタンをクリック
[セルの書式設定]-[パターン]タブで任意の色を設定し、[OK]ボタンをクリックします。

セルの色を取得するユーザー定義関数
Function CellColor(CELL)
CellColor = CELL.Interior.ColorIndex
End Function


A1に塗りつぶしの色を設定した場合のカラーコード取得
=CELLCOLOR(A1)
セル色(無色は-4142)など、セル色を集計するには
=SUMIF(C1:C7,"<>-4142",B1:B7) のようにします。

複数のシートのデータを1つのシートにまとめる [ThisWorkbook]
Sub myAllData()
'ブックの全シート内容を統合データシートにコピーします
Dim i As Integer, lRow As Long, lCol As Long, lRow2 As Long
Application.ScreenUpdating = False
sh_Togo '統合データシートの有無チェック
Worksheets(2).Range("1:1").Copy Worksheets(1).Range("A1") '列見出しコピー
For i = 2 To Worksheets.Count
With Worksheets(i)
lRow = .Cells(Rows.Count, 1).End(xlUp).Row 'A列基準(入力必須)
lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
If lRow >= 2 Then 'データが2行以上の場合にコピー
lRow2 = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
.Activate
.Range(Cells(2, 1), Cells(lRow, lCol)).Copy Worksheets(1).Cells(lRow2, 1)
End If
End With
Next i
Worksheets(1).Activate
Range("A1").Select
MsgBox "すべてのシートを統合データにコピーしました。"
Application.ScreenUpdating = True
End Sub
Sub sh_Togo()
'統合データシートを作成、確認します
Dim newSh As String, Sh As Worksheet, myFlag As Boolean
newSh = "統合データ" '統合するシート名
myFlag = False
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name = newSh Then
myFlag = True
Worksheets(newSh).Cells.ClearContents '統合旧データをクリア
Worksheets(newSh).Move before:=Sheets(1) '先頭へ移動
Exit For
End If
Next Sh
If myFlag = False Then '統合データシートを最左(先頭)へ追加
ActiveWorkbook.Worksheets.Add(before:=Worksheets(1)).Name = newSh
End If
End Sub

ブックの全シートを一括保護する
Sub AllHogo()
'全シートを保護します
Dim mySheet As Worksheet
Application.ScreenUpdating = False
For Each mySheet In ThisWorkbook.Sheets
mySheet.Protect
Next mySheet
Application.ScreenUpdating = True
End Sub


<参考>6行目を mySheet.Unprotect とすると全保護を解除します。
<参考>特定シートを保護、保護解除を指定
Sub HogoX()
Sheet2.Protect
Sheet3.Unprotect
Sheet4.Protect
End Sub

アクティブセルをカラー表示する [Sheet1]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'アクティブセルをカラー表示します
Static acCell As String
On Error Resume Next
Range(acCell).Interior.ColorIndex = xlColorIndexNone
With Target
If .Interior.ColorIndex = xlColorIndexNone Then
acCell = .Address
.Interior.ColorIndex = 36 '薄い黄色指定
End If
End With
End Sub
または、

Public myR As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'アクティブセルと選択セルをカラー表示します
If myR <> "" Then Range(myR).Interior.ColorIndex = 0
Target.Interior.ColorIndex = 6 '黄色
myR = Target.Address
End Sub


<ご注意>
上記マクロを設定した場合、セルにあらかじめ設定されている色は無色となります。

アクティブセルのある行・列を目立たせるには(条件付き書式)
条件付き書式を設定したいセル範囲を選択し、数式欄に下記数式を入力します。
行・列とも目立たせるのなら
=OR(CELL("row")=ROW(), CELL("col")=COLUMN())

列を目立たせるのであれば、条件付き書式の条件を
=CELL("col")=COLUMN()

そして、VBEを起動し [ThisWorkbook] に下記コードを記入します。
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

検索セル(A1)に検索値を入力すると、A3以下の行を検索し、そのセルに自動ジャンプ
Private Sub Worksheet_Change(ByVal Target As Range)
'検索値(a1)で列A3以下を検索ジャンプ表示します。
Dim myRow As Long, myRange As Range
myRow = Target.Row
If Target.Address = Range("a" & myRow).Address Then
If Target.Value = "" Then Exit Sub
Set myRange = Range("A2:" & Cells(Rows.Count, 1).End(xlUp).Address).Find(Range("a1").Value, lookat:=xlWhole)
If myRange Is Nothing Then
MsgBox "検索値は見つかりません。", vbOKOnly, "検索エラー"
Target.Select
Else
myRange.EntireRow.Select
End If
End If
End Sub

指定範囲を検索して、検索値と一致したセルを全選択する
<前提1>検索行範囲をA2〜A1000 としています。
<前提2>検索値を 100以上 としています。

Sub myXSearch()
'A列で指定の値を検索して選択します
On Error GoTo ErrorHandler
myX = ""
For i = 2 To 1000 '検索行範囲
If Range("A" & i) >= 100 Then myX = myX & ",A" & i '検索値指定
Next
myX = Right(myX, Len(myX) - 1)
Range(myX).Select
Exit Sub
ErrorHandler:
MsgBox "検索値は見つかりません"
End Sub

重複のないリスト作成
Sub myRavelFx()
'A列(品名),B列(数値)の場合、重複のない品名と合計をD,E列に書き出します
Dim myRow As Long
myRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & myRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("D1"), Unique:=True
Range("E1").Value = "合計"
myRow = Range("D" & Rows.Count).End(xlUp).Row
Range("E2:E" & myRow).Formula = "=SUMIF(A:A,D2,B:B)"
End Sub


<参考>E列の数式をなくして値のみとするには下記コードを End Sub の前行に追加してください。
With Range("E2:E" & myRow)
.Value = .Value
End With

重複値(A列とC列を比較)
Sub Find_Matches()
'A列とC列を比較し、重複値をB列に書き込みます
Dim CompareRange As Variant, x As Variant, y As Variant
UsedRange.Columns("B").Offset(1, 0).Clear '旧データクリア
UsedRange.Columns("A").Select '基本データ列
Set CompareRange = Range("C1:C100") '比較対象範囲
For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, 1) = x '重複書き込み
Next y
Next x
End Sub

ワークシートをコピー禁止にしたい (Sheet1)
Private Sub Worksheet_Deactivate()
Application.CutCopyMode = False
End Sub

または
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CutCopyMode = False
End Sub

指定画像を縮小してシートに挿入
Sub InsertGazo()
'選択した画像をシートに挿入します
'幅を基準に元サイズの0.5に縮小します
Dim FilePath As Variant
FilePath = Application.GetOpenFilename(",*.*")
If Not FilePath = False Then
ActiveSheet.Pictures.Insert(FilePath).Select
With Selection
.ShapeRange.LockAspectRatio = msoTrue
.ShapeRange.ScaleWidth 0.5, msoTrue
.ShapeRange.Left = ActiveCell.Left
.ShapeRange.Top = ActiveCell.Top
End With
End If
End Sub

セルサイズに合わせて指定画像をシートに挿入
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'シート上でWクリックし、セルサイズに合わせて画像挿入します
Dim WDT, HGT, CTP, CLF, PWD, PHT
Application.ScreenUpdating = False
WDT = Target.Width
HGT = Target.Height
CTP = Target.Top
CLF = Target.Left
On Error GoTo Fin
Application.Dialogs(xlDialogInsertPicture).Show
With Selection.ShapeRange
.LockAspectRatio = msoTrue
PWD = .Width
PHT = .Height
Select Case PHT / PWD
Case Is >= HGT / WDT
.Height = HGT
.Left = CLF + (WDT - .Width) / 2
Case Else
.Width = WDT
.Top = CTP + (HGT - .Height) / 2
End Select
End With
Fin: On Error GoTo 0
Application.ScreenUpdating = True
End Sub

ワード文書を開くには
Sub myDocOpen()
'指定のワード文書を開きます
Dim xFile As Object
Dim Doc As Object
Dim fPath As String
fPath = "D:\Documents\TEST.docx" '指定ファイル名とパス
Set xFile = CreateObject("Word.Application") 'Wordを起動
xFile.Visible = True 'Wordを表示
Set Doc = xFile.documents.Open(fPath) '文書を開く
End Sub


<参考>バージョンによりファイル拡張子が異なります。
MS-Word ver.〜2003 は .doc   ver.2007 は .docx と指定してください。


TOP / FAQ1 / FAQ2 / FAQ3 /上へ

Copyright © 2012 TOMBO. All rights reserved.